2

I'm getting into the database API and it's really interesting how it works. The function db_query() makes querying the database very easy. However, I don't quite understand placeholders and the relationship between placeholders and $args parameter.

Example #1, the first comment on the doc page, can be written as: $result = db_query('SELECT n.field1, n.field2, ... FROM {table} n WHERE n.field0 = :field0', array(':field0' => $field0value)); foreach ($result as $record) { // Data are in $record->field1, $record->field2, ... The db_query function has two parameters; the first is an SQL query and the second is an array definition. The two are related by ":field0", which is called a placeholder. The fields and table are also modified by something called "n"; I don't know what that is.

I found that the above code works. However, I also found that it works and yields the same results without the n's, without the placeholder, and without the array: (The function syntax says the default if the array is not provided is simply "array()".) 'SELECT field1, field2, ... FROM table WHERE field0 = ' . $field0value

I tried this because I need to use complex criteria in which it is not clear what to do with the placeholder and array: SELECT * FROM table WHERE concat(if(field1 = "", field2, field1), "value1") = "value2"

I ran this in phpMyAdmin and it works fine. It works by first looking at field1 and if there's something there, it uses that but if it's empty then it takes the value from field2. It then combines that with "value1" and selects records for which the result equals "value2".

So I put that in db_query like so: db_query('SELECT * FROM {table} WHERE concat(if(field1 = "", field2, field1), "value1") = "value2"');

which resulted in the message: PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause': SELECT * FROM {table} WHERE concat(if(field1 = "", field2, field1), "value1") = "value2"; Array ( ) in include_once() (line 87 of ....php).

Drupal does not seem to be complaining about the use of the concat and if functions, so it seems to be capable of handling them. The symbol between the words "column" and "in" above is a pair of single quotes. So it looks like Drupal is looking for a column designation somewhere and not finding one. My guess is it's looking for something in the empty array. Do I need to use one or more placeholders and some form of array construct in order to get this to work?

Thanks for your help.

  • 1
    It's the ANSI_QUOTES thing again, the problem is this bit specifically: field1 = "". With ANSI_QUOTES on, MySQL interprets "" as an identifier, and so thinks you're looking for an empty column name. It'll do the same thing for "value1". Just use single quotes and it'll work – Clive May 15 '15 at 18:19
  • @Clive - Wow, that was too easy! If you post it as an answer, I'll select it. It makes my code strange because I usually wrap strings in singles with doubles inside and I've reversed that for this one line, but that doesn't matter. New problem now: I can access the data with "foreach ($result as $record) {...}" and "$record->field1", etc., but there is supposed to be only one record so I want to test for that and access it. I get errors with "$result->rowCount" and "$result->fetchAssoc". What is the right way to do that? – NewSites May 15 '15 at 19:25
1

The answer provided in the comment above did get the code to work, but according to Jaypan in the Drupal forums, using that solution is a security risk. The error was caused by not using placeholders and the associated array argument, and the purpose of those devices is to prevent SQL injection attacks. The solution of reversing the quotation marks was able to stop the error messages, but at the cost of leaving open a security hole in the code. Jaypan gave me the code to implement the complex criteria with placeholders:

<?php
db_query('SELECT * FROM {table} WHERE concat(if(field1 = :empty, field2, field1), :value1) = :value2',
array(':empty' => '', ':value1' => 'value1', ':value2' => 'value2');
?>

I've implemented that and it works. The pattern seems clear enough to be able to adapt it to other complex criteria in the future, although it would be nice if we had some documentation of placeholders and their syntax.

===============

Edit: It turns out there is some documentation of placeholders, although it's not comprehensive and the discussion with Jaypan (linked above) helped to clarify things.

  • 1
    I agree with Japan's comment. No matter the value contains a user input ("value") or not (""), try to keep them separate and pass as arguments. – AyeshK May 16 '15 at 23:48
  • There was no security risk in your original code, SQL injection can't happen if you're using static strings. It's good practice to use placeholders even with static strings, of course, but it's not a security risk if you don't. If you're receiving user input that's a different story, but my comment was only pointing out the syntax error in your MySQL statement – Clive May 17 '15 at 15:26
  • Yes, Jaypan also said in his third comment in that thread that there was no security issue for that particular case, but that it's a good idea to use placeholders anyway as best practice. It's strange because the error message went away by either switching the quotation marks or using a placeholder, so was the error using the wrong quotes or not using a placeholder? I'm thinking the error was caused by db_query complaining that it didn't have a placeholder, so switching the quotes is a work-around as opposed to a fix. But I'm still new and inexperienced here, so could easily be wrong. – NewSites May 18 '15 at 13:11
0

Looks like you problem is within the field1 = "" ... Try to use single quotes and it should work.

Not sure why Clive did not post his comment as "the" answer (maybe he fears downvotes?), which is why I did so now ... So that NewSites has something to consider for "accepting" ...

PS: your new problem should be moved to a new question I think.

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.