Make WordPress Core

Opened 14 years ago

Closed 14 years ago

#11318 closed defect (bug) (duplicate)

$wpdb->prepare() Chokes on single literal percentage chars?

Reported by: miqrogroove's profile miqrogroove Owned by: ryan's profile ryan
Milestone: Priority: normal
Severity: normal Version: 2.8.4
Component: Inline Docs Keywords:
Focuses: Cc:


This might be a documentation issue, but I'm feeling a bit clueless tonight after reading

var_dump($wpdb->prepare("SELECT 1 WHERE col1 LIKE 'image%'"));
//returns bool(false)

prepare() seems incapable of even the most simple LIKE query syntax.

Change History (13)

#1 @Denis-de-Bernardy
14 years ago

You'll also get false with wpdb->get_results() with that example sql. :-)

that said, you are correct that it hates LIKE statements:

Warning: vsprintf() [function.vsprintf]: Too few arguments in /Users/denis/Sites/sem-pro/wp-includes/wp-db.php on line 539

#2 @dd32
14 years ago

  • Milestone 3.0 deleted
  • Resolution set to worksforme
  • Status changed from new to closed

Due to Prepare utilising the printf syntax of '%s' and alike, Its impossible to simply add a single % to a query.

You only need to use Prepare() when making a query which has params, not a hardcoded query.

The 2 options are:

  1. escape the % in printf style: var_dump($wpdb->prepare("SELECT 1 WHERE col1 LIKE 'image%%'"));
  2. add it as a param: var_dump($wpdb->prepare("SELECT 1 WHERE col1 LIKE %s", 'image%'));

Closing as worksforme, as those 2 options work around it. Its not worth implementing our own vsprintf just for that single function, If it can be done in other ways.. i think its worth it due to the benefits which the current implementation brings.

#3 @miqrogroove
14 years ago

I would have liked this documented either in wp-db.php or in the codex. When sanitization function don't work as expected, people don't use them.

The route I ended up taking looked something like

"SELECT 1 WHERE" . $wpdb->prepare(" x = %s ", $var) . "AND col1 LIKE 'image%'"

#4 @hakre
14 years ago

Related: #11608 / wpdb->prepare() is defective by design.

#5 @miqrogroove
14 years ago

Having participated in hakre's thread, the 2 patterns offered by dd32 above and mine above are all looking undesirable for my own code. I've dropped my instances of prepare() and substituted the good old $wpdb->_real_escape() pattern. It works with my queries, and I know it's faster and more secure than $wpdb->prepare(). It became the obvious, simple solution.

#6 @hakre
14 years ago

Regarding the original report:

var_dump($wpdb->prepare("SELECT 1 WHERE col1 LIKE 'image%'"));
//returns bool(false)

I suggest to use the correct syntax for percentage literals to preserve them in the query:

var_dump($wpdb->prepare("SELECT 1 WHERE col1 LIKE 'image%%'"));
//returns string("SELECT 1 WHERE col1 LIKE 'image%'")

This is the documented behaviour of the sprintf page.

The WPDB->prepare() functions documentation as well as the codex would benefit from a touchup explaining the user about all three format specifiers it supports: %%, %d and %s.

#7 @hakre
14 years ago

Realted: #11608

#8 @hakre
14 years ago

  • Component changed from Database to Inline Docs
  • Milestone set to 3.0
  • Resolution worksforme deleted
  • Status changed from closed to reopened

Looks like this is sort of a documentation issue, I'll reopen it therefore. A Patch in #11608 does address it as well.

#9 @hakre
14 years ago

  • Summary changed from $wpdb->prepare() Chokes on LIKE conditions? to $wpdb->prepare() Chokes on single literal percentage chars?

#10 @Denis-de-Bernardy
14 years ago

  • Keywords bug-hunt added

#11 @Denis-de-Bernardy
14 years ago

  • Keywords featured added; bug-hunt removed

#12 @hakre
14 years ago

The documentation issue has just been partly taken care of with [13314], the docblock comment of wpdb::prepare() now reflects the '%' parameter.

Related to #11644, for completeness there is another comment left to commit:

	 * <code>
	 * wpdb::prepare( "SELECT * FROM `table` WHERE `column` = %s AND `field` = %d", "foo", 1337 )
	 * wpdb::prepare( "SELECT DATE_FORMAT(`field`, '%%c') FROM `table` WHERE `column` = %s", 'foo' );
	 * </code>

Current version only has the first one, but the second one is the better example to show % usage.

#13 @nacin
14 years ago

  • Keywords featured removed
  • Milestone 3.0 deleted
  • Resolution set to duplicate
  • Status changed from reopened to closed
Note: See TracTickets for help on using tickets.