0

I have a separate custom table in my database for each language I'm providing for my website. I need to select the correct table dynamically from a variable that is sent from a POST request.

First I was trying to to use $wpdb->prepare but then I read that it can't really handle the name of the table since it will force it into a string.

The other solutions that I saw looked something like this:

$foods = $wpdb->get_results("SELECT * FROM $sanitized_search_language WHERE dbID = $sanitized_search_text", ARRAY_A);

That will give me the following error:

"<div id=\"error\"><p class=\"wpdberror\"><strong>WordPress database error:</strong> [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near &#039;WHERE dbID = 719&#039; at line 1]<br /><code>SELECT * FROM  WHERE dbID = 719</code></p></div>[]"

I also saw the variables wrapped in curly brackets in some solutions like this:

$foods = $wpdb->get_results("SELECT * FROM {$sanitized_search_language} WHERE dbID = {$sanitized_search_text}", ARRAY_A);

But that is also giving me an error.

How can I select the custom table I want to query based on a dynamic variable?

3
  • What is $sanitized_search_language? It appears to be blank? Also, accepting the table name from a post request is astonishingly reckless if not done properly. May 11 at 7:48
  • @JacobPeattie Hi! Thanks for pointing me to the right direction! The value was indeed empty. Is it properly enough if I use sanitize_text_field() for the search query and limit the options to the allowed table names with an if-statement?
    – user44109
    May 11 at 8:55
  • 1
    Whitelisting values is an appropriate solution. May 11 at 9:06
0

I will start with the question.

From the sql error output we see this SELECT * FROM WHERE dbID = 719

This tells us that $sanitized_search_language has no value, so check it first.

Now for the BIG problem with this query!

You used get_results with a sql query that has variables as is, get_results doesn't escape the variables.

You must use prepare when passing variables into an sql query in order to prevent sql injections.

see wpdb::get_results, the first user contribution shows a great example on how to implement this

4
  • Thanks for helping! The value was indeed missing and after I fixed that part, then the dynamic query started working. Is it enough to secure this if I use sanitize_text_field and also an if statement where I check that the value is one of the allowed table names? I read elsewhere that the wpdb->prepare statement won't work for the table name (only parameter values).
    – user44109
    May 11 at 8:57
  • I see no reason why it shouldn't with table name, all it does is escape values and prepares them to be a valid and injection free values. Try it, it should work. About sanitize_text_field, its used for xss and not to prevent sql injection May 11 at 9:14
  • I tried it and couldn't get it to work. Also the answers in this Stack Exchange question seem to suggest it's not possible to bind a dynamic table name with the prepare statement: stackoverflow.com/questions/11312737/…
    – user44109
    May 11 at 10:32
  • Ok, so you would need to create your own filter to "prepare" the table name, if it wont play nice with dynamic table names than you'll need to do the sanitization and validation May 11 at 10:35

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.