WordPress.org

Make WordPress Core

Opened 16 months ago

Closed 16 months ago

Last modified 16 months ago

#23064 closed defect (bug) (invalid)

support str_to_date on $wpdb->prepare

Reported by: jperelli Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.4.2
Component: Database Keywords: needs-patch dev-feedback
Focuses: Cc:

Description

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');"));

Change History (3)

comment:1 dd3216 months ago

  • Milestone Awaiting Review deleted
  • Resolution set to invalid
  • Status changed from new to closed

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/

comment:2 jperelli16 months ago

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!

comment:3 dd3216 months ago

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.

Note: See TracTickets for help on using tickets.