support str_to_date on $wpdb->prepare

[jperelli]

I was trying to use the function str_to_date from mysql in a SQL raw query using $wpdb to query de db.

I realized that $wpdb->prepare is not letting the query execute because of the presence of str_to_date

Steps to reproduce:

global $wpdb;

echo $wpdb->get_var($wpdb->prepare("select STR_TO_DATE('200442 Monday', '%X%V %W');"));

[dd32]

The issue isn't that STR_TO_DATE is present, but because STR_TO_DATE() uses the same place holder syntax that wpdb::prepare() uses.

You'll need to escape the %'s in the call as such for it to pass through wpdb::prepare()

$wpdb->prepare("select STR_TO_DATE('200442 Monday', '%%X%%V %%W');")

or

$wpdb->prepare("select STR_TO_DATE('200442 Monday', %s);", '%X%V %W' )

However, wpdb::prepare() should NOT be called with only a SQL string, it's only designed for inserting parameters into the string, See this post for details: ​http://make.wordpress.org/core/2012/12/12/php-warning-missing-argument-2-for-wpdb-prepare/

[jperelli]

You are right, thanks!

I thought wpdb::prepare() was like addslashes or mysql_escape_string, and made some sort of crazy magic to secure the query, but is more like sprintf.

Sorry, didn't want to waste your time. I see now it is on the docs ​http://codex.wordpress.org/Class_Reference/wpdb#Protect_Queries_Against_SQL_Injection_Attacks

I think I read it all but that part. :( Must go to sleep.

Thank you for teaching me!

[dd32]

I thought wpdb::prepare() was like addslashes or mysql_escape_string, and made some sort of crazy magic to secure the query, but is more like sprintf.

It does escape the arguements to protect against SQL injection and the alike, but in order for it to do so, the arguements need to be passed in as seperate items, with place holders in the original SQL statement. internally it does use sprintf() to insert the escaped data however.