PDOException "column not found" but does exist

Question

This is on Drupal 7.39 with Domain Access and Ubercart

When pressing "checkout" button the site returns and error which shows in the dblog as:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'uc_payment_method_credit_checkout' in 'where clause': SELECT value FROM {variable} WHERE name =uc_payment_method_credit_checkout LIMIT 0, 1; Array ( ) in uc_domain_variable_get() (line 291 of /. . . /uc_domain.module).

Line 291 of the uc_domain_module is:

$result = db_query_range("SELECT value FROM {variable} WHERE name =" . $variable, 0, 1)->fetchField();

The column exists in the variable table and I triple checked it for an exact format match (they all exist for the variable_set commands I placed to be used in the install file)

This module is a Drupal 7 version module but is not an official project owned or maintained by anyone though it was originally written by the Drupal 6 project author and maintainer and released by him at https://www.drupal.org/node/946574. I am working with that released file as modified by "henk" - but this section is unchanged by "henk" either

The equivalent command in the Drupal 6 version is near identical

D6 version starts . . .
$result = db_result(db_query("SELECT value FROM {variable} WHERE name = '%s'", $variable));

D6 version ends . . .

I put a space in the => " . . . WHERE name = " . $variable . . ." to match the D6 version

But the error now simply reads " . . . WHERE name = uc_payment_method_credit_checkout . . . " with the space at "name = "

It seems to work -partly as it created an order entry in uc_domain_orders when i tries to test it - somehow - even though the jump to checkout failed.

This is not not like any other "column not found" issue I have searched here.

Anyone have any words of wisdom or ideas ???

Answer 1

Concatenating variables into an SQL query is usually a bad idea (search for SQL injection and you'll see why). The D6 version you're starting with:

$result = db_result(db_query("SELECT value FROM {variable} WHERE name = '%s'", $variable));

uses parameters, you just need to convert that to use the newer query substitution in D7. The docs for db_query() have some good examples, essentially what you need for this particular query is:

$query = 'SELECT value FROM {variable} WHERE name = :name';
$params = array(':name' => $variable);
$result = db_query($query, $params)->fetchField();

Answer 2

If you reported exactly both the error message and the code part, then the error is pretty "normal".

The D6 version
"SELECT value FROM {variable} WHERE name = '%s'"
includes simple quotes around %s, so the resulting query in the case you cited is
SELECT value FROM {variable} WHERE name ='uc_payment_method_credit_checkout' LIMIT 0, 1

At the opposite in your D7 modified version
"SELECT value FROM {variable} WHERE name =" . $variable
you didn't keep the quotes, so the resulting query becomes
SELECT value FROM {variable} WHERE name =uc_payment_method_credit_checkout LIMIT 0, 1
and without quotes SQL interprets uc_payment_method_credit_checkout as a column-name rather than a string.

This was only to explain why the error happens. In the other hand I totally agree with Clive recommendations.

Answer 3

i am not the author of any of this code; but Clive gave me the clue - while the question was on hold as off topic - so I reformulated the query to

 $result = db_query_range("SELECT value FROM {variable} WHERE name = :name", 0, 1, array(':name' => $variable))->fetchField();

Of course Clive's version works equally as well