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

[miqrogroove]

This might be a documentation issue, but I'm feeling a bit clueless tonight after reading ​http://codex.wordpress.org/Function_Reference/wpdb_Class

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

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

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

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.

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%'"

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

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.

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.

Realted: #11608

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

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.

#12362