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

Question

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

wpdb uses mysql_query() if the MySQLi functions are not available or if the WordPress constant named WP_USE_EXT_MYSQL is defined.
MySQLi has support for multiple statements using mysqli_multi_query(), but (as of writing), wpdb does not use that function.

Notes

Related Posts: