0

I have a pb using wpdb->prepare and string placeholders.

If I do:

$sql = $wpdb->prepare("SELECT * FROM %s AS T1 LEFT JOIN $table_users AS T2 ON T1.id_assure = T2.ID WHERE T1.id = %d",$table_dossiers,$id);

then the generated query is:

SELECT * FROM 'drk4_2_dracard_dossiers' AS T1 LEFT JOIN drk4_users AS T2 ON T1.id_assure = T2.ID WHERE T1.id = 33

And the query fails because of the quotes around the first table name provided by placeholder: 'drk4_2_dracard_dossiers'

conversely, if I write both table names in the query (without using a placeholder), it works:

$sql = $wpdb->prepare("SELECT * FROM $table_dossiers AS T1 LEFT JOIN $table_users AS T2 ON T1.id_assure = T2.ID WHERE T1.id = %d",$id);

The generated query is:

SELECT * FROM drk4_2_dracard_dossiers AS T1 LEFT JOIN drk4_users AS T2 ON T1.id_assure = T2.ID WHERE T1.id = 33

And it works fine...Do you know what I may do to correct the first code and still use the placeholders for both tables ? Thank you

4
  • 1
    Is the table name coming from user input? You shouldn't need to escape it if you know what it's going to be. Mar 10, 2018 at 11:38
  • the table name is formed like this $table_dossiers = $wpdb->prefix.'dracard_dossiers';
    – Pierre
    Mar 10, 2018 at 12:30
  • I have the feeling that "prepare" only allows to replace the placeholders which are in the VALUES of the query (and not the field or table names): that would explain why the string are quoted...
    – Pierre
    Mar 10, 2018 at 12:35
  • 2
    Well that's what it's for. That's what I was getting at, you don't need to prepare every variable, you're supposed to be preparing inputd you can't trust, or contains characters unsafe for SQL. Normally that wouldn't include table or column names. You know and can trust the table variable so what does escaping it accomplish? Mar 10, 2018 at 12:49

1 Answer 1

1

Using %i instead of %s puts backticks ` around it instead of ' that works for table names

1

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.