how to execute different sql query in non-sanitized $wpdb->get_results function

admin管理员组

文章数量:1310297

I saw $wpdb->get_results that function is not sanitized by core code($wpdb is get_results escaped).

For example, in following code

$wpdb->get_results( "DELETE FROM `wp_postmeta` WHERE `meta_key` = 'likeiwould' AND `meta_value` = '{$array['item']}'" );

That should execute a mysql sleep command, but not working.

$array['item'] = "123';SELECT+sleep(10)'"

Result:

DELETE FROM `wp_postmeta` WHERE `meta_key` = 'likeiwould' AND `meta_value` = '123';SELECT+sleep(10)''

So, if $wpdb->get_results is not sanitized by core code, why we can't execute different sql query in it? Thanks.

I saw $wpdb->get_results that function is not sanitized by core code($wpdb is get_results escaped).

For example, in following code

$wpdb->get_results( "DELETE FROM `wp_postmeta` WHERE `meta_key` = 'likeiwould' AND `meta_value` = '{$array['item']}'" );

That should execute a mysql sleep command, but not working.

$array['item'] = "123';SELECT+sleep(10)'"

Result:

DELETE FROM `wp_postmeta` WHERE `meta_key` = 'likeiwould' AND `meta_value` = '123';SELECT+sleep(10)''

So, if $wpdb->get_results is not sanitized by core code, why we can't execute different sql query in it? Thanks.

• wpdb
• sql

Share Improve this question asked Jan 15, 2021 at 5:15 bluewayblueway 2366 bronze badges 3

• "That should execute a mysql sleep command" - try the generated SQL query in phpMyAdmin or MySQL command line interface - do you get any results, or does the query results in an (unknown) error? But really, why would you want to execute vulnerable SQL queries? And always sanitize user-supplied data or data from unknown/untrusted sources; e.g. use $wpdb->prepare(). – Sally CJ Commented Jan 15, 2021 at 7:43
• 1 Hey @SallyCJ I got it why it's not working, get_results function not working when you give multiple SQL queries after semicolon ; and also adding backslashes after quotes ', ". It was experimental situation now is okay, thank for the response. – blueway Commented Jan 15, 2021 at 16:08
• Ah my bad, sorry for the confusion, I completely misunderstood the actual question.. :p But I hope my answer helps you? :) – Sally CJ Commented Jan 15, 2021 at 19:09

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

if $wpdb->get_results is not sanitized by core code, why we can't execute different SQL query in it?

Because the wpdb class uses (see wpdb::_do_query() ) mysqli_query() (by default) or mysql_query(), and unfortunately these functions do not support multiple queries such as SELECT <query>; INSERT <query>; SELECT <query>.

Therefore, if you want to execute multiple SQL queries using wpdb, you would need to execute each query individually like so:

// You cannot do this:
// And I mean, regardless the query is escaped/safe or not, this won't work.
$results = $wpdb->get_results( "SELECT <query>; INSERT <query>; SELECT <query>" ); // doesn't work

// But you can do this:
$results = $wpdb->get_results( "SELECT <query>" );
$rows = $wpdb->query( "INSERT <query>" ); // this works, but you should use $wpdb->insert(), though
$results = $wpdb->get_results( "SELECT <query>" );

And in reply to your comment:

get_results function not working when you give multiple SQL queries after semicolon ; and also adding backslashes after quotes ', "

About that "semicolon ;" (e.g. as in DELETE <query>; SELECT <query>), yes you're correct.

And about that "adding backslashes", yes, you're also correct, but the better/preferred way is to use wpdb::prepare() to prepare the SQL query for safe execution.

// We used the same query, but different data.

// Query 1: Not escaped, but data is good, so no errors thrown.
$value = '"Foo"'; // intentionally not escaped
$results = $wpdb->get_results( "SELECT ID, post_title FROM $wpdb->posts WHERE post_title = '{$value}' LIMIT 2" );
// Generated SQL: SELECT ID, post_title FROM wp_posts WHERE post_title = '"Foo"' LIMIT 2

// Query 2: Not escaped, data is bad, thus results in a syntax error!
$value = "'Foo'"; // intentionally not escaped
$results = $wpdb->get_results( "SELECT ID, post_title FROM $wpdb->posts WHERE post_title = '{$value}' LIMIT 2" );
// Generated SQL: SELECT ID, post_title FROM wp_posts WHERE post_title = ''Foo'' LIMIT 2

// Query 3: Works good - data escaped manually, and no errors thrown.
$value = '\'Foo\''; // backslashes added manually
$results = $wpdb->get_results( "SELECT ID, post_title FROM $wpdb->posts WHERE post_title = '{$value}' LIMIT 2" );
// Generated SQL: SELECT ID, post_title FROM wp_posts WHERE post_title = '\'Foo\'' LIMIT 2

// Query 4: Works good - data not escaped, but wpdb::prepare() is used, so no errors thrown.
$value = "'Foo'"; // intentionally not escaped
// But then, this query uses wpdb::prepare().
$results = $wpdb->get_results( $wpdb->prepare( "SELECT ID, post_title FROM $wpdb->posts WHERE post_title = %s LIMIT 2", $value ) );
// Generated SQL: SELECT ID, post_title FROM wp_posts WHERE post_title = '\'Foo\'' LIMIT 2

Notes

1. wpdb uses mysql_query() if the MySQLi functions are not available or if the WordPress constant named WP_USE_EXT_MYSQL is defined.

2. MySQLi has support for multiple statements using mysqli_multi_query(), but (as of writing), wpdb does not use that function.

本文标签： how to execute different sql query in nonsanitized wpdbgtgetresults function