2

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.

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
    Jan 15 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
    Jan 15 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
    Jan 15 at 19:09
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.

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.