mysql - Wpdb query with dynamic table name

admin管理员组

文章数量:1292977

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?

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?

• mysql
• wpdb

Share Improve this question asked May 11, 2021 at 7:35 user44109user44109 3344 bronze badges 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. – Jacob Peattie Commented May 11, 2021 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 Commented May 11, 2021 at 8:55
• 1 Whitelisting values is an appropriate solution. – Jacob Peattie Commented May 11, 2021 at 9:06

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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

本文标签： mysqlWpdb query with dynamic table name