I don't know if this is really a TAC_Lite issue or a db_rewrite_sql issue.

Trying to honor the ideas in the Writing secure code article, I changed my statement to this:

    $sql_count = db_rewrite_sql('SELECT COUNT(DISTINCT(n.nid)) FROM {node} n WHERE n.status = 1 AND n.type = \'pinkslip\' AND n.uid = %d', $auid);

However, I started getting SQL errors, so I stuck in a drupal_set_message to track it down. This is what happens to the statement after the db_rewrite_sql call:

SELECT COUNT(DISTINCT(n.nid)) FROM {node} n INNER JOIN {node_access} na ON na.nid = 6.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'tac_lite') OR (na.gid = 2 AND na.realm = 'tac_lite') OR (na.gid = 0 AND na.realm = 'tac_lite'))) AND ( n.status = 1 AND n.type = 'pinkslip' AND n.uid = %d)

As you can see, my $auid parameter (value is 6) gets stuck into the INNER JOIN ON clause rather than where it belongs. And the "%d" never gets resolved.

Guess I have to go back to just concatenating rather than doing this "right."

Comments

erdemkose’s picture

erdemkose commented
Status: Active » Closed (works as designed)

Your db_rewrite_sql call is wrong. You can see the right way on http://api.drupal.org/api/5/function/db_rewrite_sql

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

Thanks for the response. Actually, the API is even more confusing to me. It looks more like I don't need rewrite at all.

csc4’s picture

csc4 commented

I've got the same problem - db_rewrite_sql queries which were fine in 4.7 are blowing up on trying to upgrade to 5.

In my case they're introducing completely unwanted DISTINCT(n.nid) which breaks MySQL5 (and appears to be a problem going back to 2006! - nailing down db_rewrite_sql (mysql 5))

The simple fix seems to be to remove the rewrite but I understood the rewrite was the 'right' way to do this type of query. Is that no longer the case or is there a more 'Drupal' way to do the following query:

   $result = db_query_range("SELECT n.nid FROM {node} n WHERE n.type = 'story' AND n.status = 1 ORDER BY n.created DESC", 0, 16);