0

I'm using wpdb to get a row of a table

$file = $wpdb->get_row( "SELECT *
                         FROM {$wpdb->prefix}vip_files 
                         WHERE {$file_hash} = `hashcode`" );

and the error is:

[Unknown column '4075c8514267d7926cd7f0cde577da2c' in 'where clause']   
SELECT * FROM wp_vip_files WHERE 4075c8514267d7926cd7f0cde577da2c = `hashcode`

4075c8514267d7926cd7f0cde577da2c is the value of $file_hash

it is using the variable as a column name. if I put the value instead of variable, it will work but with variable I get error.

3
  • 3
    Well, column values should be wrapped with quotes, so you should use something like '$file_hash' and not {$file_hash}. And you really should escape the variable value, e.g. using wpdb::prepare().
    – Sally CJ
    Feb 25, 2021 at 23:17
  • thank you for your answer. it worked. the edited code is: $file = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$wpdb->prefix}vip_files WHERE '{$file_hash}' = hashcode")); Feb 26, 2021 at 6:24
  • Actually, your code is not correct, but please see my answer for the details. And if my answer answered your question, please consider marking it as correct (just tick the check-mark next to my answer).. :)
    – Sally CJ
    Feb 26, 2021 at 13:55

1 Answer 1

1

The error in question happened because you did not properly build your SQL query/statement:

  • The column name in your query is hashcode, and you are trying to get the row where the column value matches the value of the $file_hash variable, i.e. WHERE {$file_hash} = `hashcode`.

  • But because the value is a string/text (and not a number), then it needs to be wrapped in quotes like so: '$file_hash'.

  • And if you don't do that, then the value would be seen as the column name instead of the supposedly column value.

So fixing the issue is basically easy — just wrap the variable with quotes like so:

SELECT *
FROM {$wpdb->prefix}vip_files 
WHERE '$file_hash' = `hashcode`

However, that query is highly prone to SQL injection attacks because the value of $file_hash is not escaped, so you need to escape it, and I would suggest you to use wpdb::prepare() like so:

$query = $wpdb->prepare( "SELECT *
    FROM {$wpdb->prefix}vip_files 
    WHERE %s = `hashcode`",
    $file_hash
);

$file = $wpdb->get_row( $query );

Also, remember that wpdb::prepare() requires at least two parameters: the SQL query with placeholders like %s; and the replacement values for the placeholders used in that SQL query, like so: $wpdb->prepare( "SELECT * FROM table_name WHERE some_column = %s", 'column value' ).

  • So this is actually incorrect: (this is what you used as you said in the comments)

    $wpdb->prepare("SELECT * FROM {$wpdb->prefix}vip_files WHERE '{$file_hash}' = `hashcode`")

    More specifically, yes, that code does work in that MySQL would not throw any error. But it will result in a PHP notice saying "wpdb::prepare was called incorrectly". And what's worse is, the above code will not actually going to escape the $file_hash variable!

  • And the correct code is this — I used the %s placeholder and the $file_hash is now the second parameter for $wpdb->prepare():

    $wpdb->prepare("SELECT * FROM {$wpdb->prefix}vip_files WHERE %s = `hashcode`", $file_hash)

So make sure to properly call wpdb::prepare() and note that escaping the variable value not only ensures the query to be safe from SQL injections, but escaping would also help us avoid syntax errors in SQL. I mean, when the variable value is something like 90's favorites (note the quote).

And I hope that helps, and do check out the full class reference for more details about the class and its methods/functions and properties. :)

2
  • thank you for the complete answer. now I know what to do. you are saying that when we use prepare, we should use placeholders for variables to escape them and the error was because of the variable type. Feb 26, 2021 at 16:56
  • Yes, that's right - if $file_hash was a number like 123, then you'd get WHERE 123 = hashcode which would work, i.e. there'd be no error thrown by MySQL.
    – Sally CJ
    Feb 26, 2021 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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