By schommer on

I've tried everything I can think of, and this error wont' go away. I've read all the topics about errors of similar nature and none of them make any sense (or seem to target my issue. So, unfortunately the forums are my last resort because I'm lost in confusion :-|

This is my sql statement:

$query = "SELECT r.rid, r.tid FROM {acidfree_roles} r";
$roleObj = db_query( db_rewrite_sql( $query ) );

Now, when I run this as the Admin user I get all the data I need back from the SQL query, but when I run it as any other users I get the 'red box' on my node creation call (this code is in a form_alter for the image module). The error I get is:

user warning: Unknown column 'n.nid' in 'on clause' query: SELECT r.rid, r.tid FROM acidfree_roles r INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) in /home/sites/mystudentbible.com/public_html/includes/database.mysql.inc on line 172.

Now, where I'm lost... the reason column n.nid doesn't exist? I don't have one in my acidfree_roles table that I personally created for my module. Why the heck is it taking all that other crap on the end of my simple SQL statement with all that JOIN information?

From what I can tell, there have been some audit trails talking about bad SQL written join statements with specific versions of SQL. But... I don't know how this could be the case running a stock version of drupal (as I'm sure thousands of other people would find the inability to write any SQL statements).

It only happens with every other user but the root user? Any ideas? I'm at a loss.

⋅ Categories: Drupal 5.x

Comments

davesgonebananas’s picture

davesgonebananas commented

I think you should specify primary_table and primary_field in the db_rewrite_sql function. See the APi documentation for details.

pwolanin’s picture

pwolanin commented

Leave db_rewrite_sql out of this - it's not going to be relevant.

---
Work: BioRAFT

schommer’s picture

schommer commented

Woah, removing the db_rewrite_sql statement did it! Now, what is the policy on using that? Only when the user supplies data to protect against injections? I've always tried to use it because I'm overprotective.

Is that really the solution? or is it a "bug" of some type and I'm just getting around it? Just curious, the fact that it works makes me want to kiss you (in a friendly manner of course!) :-)

Derrick

cog.rusty’s picture

cog.rusty commented

The API says that it "Rewrites node, taxonomy and comment queries." (http://api.drupal.org/api/5/function/db_rewrite_sql).

The 'n' alias was a default from its parameters list, probably expecting that your query was already selecting FROM the node table.

pwolanin’s picture

pwolanin commented

the rewrite is for access control - it provides no protection against SQL injection.

SQL injection protection is achieved by using appropriate placholders for variable params (%d, '%s', etc.)

---
Work: BioRAFT