0

in my custom module, i am executing a query.

global $user;
$id = $user->uid;
print_r($id);
$query = db_query('SELECT n.nid, c.uid, n.title, f.body_value, fm.uri
FROM challenges c
LEFT JOIN node n ON n.nid = c.cid
LEFT JOIN field_data_body f ON n.nid = f.entity_id
LEFT JOIN field_data_field_challenge_pic fp ON n.nid = fp.entity_id
LEFT JOIN file_managed fm ON fp.field_challenge_pic_fid = fm.fid ');
**// WHERE c.cid = $id ');** 

when the where clause is commented out the query runs fine. I check it using:

foreach($query as $q)
{
            print_r($q);
}

But when i add the where clause i get an error: Syntax error or access violation...on the line where i write the where clause. How can i achieve this. Thanks

  • 5
    First things first, watch this Acquia video about SQL Injection. If you don't understand 100% what it's saying, keep watching it over and over again until you do :) Once you understand SQL injection, and how it's combated in Drupal, your code will naturally change. If you still get the error once you've made the changes, edit the question and someone will be able to help – Clive Feb 12 '13 at 14:30
2

While db_select() is recommended to use in D7, you can still use db_query() using named placeholders.

$result = db_query('SELECT n.nid, c.uid, n.title, f.body_value, fm.uri
                    FROM challenges c
                    LEFT JOIN node n ON n.nid = c.cid
                    LEFT JOIN field_data_body f ON n.nid = f.entity_id
                    LEFT JOIN field_data_field_challenge_pic fp ON n.nid = fp.entity_id
                    LEFT JOIN file_managed fm ON fp.field_challenge_pic_fid = fm.fid 
                    WHERE c.cid = :cid', array(':cid' => $id)); 

if ($result) {
    while ($row = $result->fetchAssoc()) {
      // Do something with:
      //    $row['nid']
      //    $row['title']
    }
}

It prevents SQL injection also.

  • @Sithu...now no error is showing but the only the query is displayed ..the results are not...however when removing the condition even the results are displayed like the title(c.title), body(f.body_value) and so on...what is happening here. I wrote the same query in php myadmin its working fine – user145078 Feb 13 '13 at 5:04
  • @Shradha, I updated my example. I hope you would do like that. With or without condition will have different results. Are you sure you have some records with WHERE c.cid = xxx. – Sithu Feb 13 '13 at 5:22
  • @Sithu...thanks..i will try that now...and yes i have checked and rechecked the tables that there are records with c.cid = xxx... – user145078 Feb 13 '13 at 5:24
3

Clive's comment is right. You should understand the reasons why, however, I will give you the short answer ;)

Use db_select()

$query = db_select('challenges', 'c');
$query->fields('c');
$query->condition('c.cid', $id, '='); 
$result = $query->execute();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.