sprintf error LIKE %%?%%

Question

I'm having some difficulties understanding the errors when using sprintf.

Current query is:

$sql = sprintf('SELECT *
                FROM %s
                INNER JOIN %s a ON (a.user_id = created_by)
                LEFT JOIN %s b ON (b.user_id = modified_by)
                WHERE provider_name LIKE "%%?%%"
                LIMIT ?,?', TABLE_NAME1, TABLE_NAME2, TABLE_NAME3);
$q = $this->db->query($sql, array($q, $page['si'], (int)$offset));

The error occurs on this line: WHERE provider_name LIKE "%%?%%"

I've tried changing it many times using the following:

WHERE provider_name LIKE "%%%?%%%"
WHERE provider_name LIKE "%?%"
WHERE provider_name LIKE %%?%%
WHERE provider_name LIKE %%%?%%%

I still get errors..

Errors:

A PHP Error was encountered

Severity: Warning

Message: sprintf(): Too few arguments

=====================================

A PHP Error was encountered

Severity: 4096

Message: Object of class CI_DB_mysql_result could not be converted to string

Filename: database/DB_driver.php

Line Number: 604

How should I perform this properly using sprintf?

Note: I'm using codeigniter so I'm using ? for querying.

Are you sure? Recheck line and file of error message. This works: http://3v4l.org/QNcl8#v430 — sectus, Jul 07 '14 at 01:10
What's the point of using place holders when the column names for the tables are explicit? — Kermit, Jul 07 '14 at 01:15
@Kermit - to prevent sql injection on user input, they assume you will know the schema of your own database, and indeed you can run SHOW query to get the schema of a table and with a bit of code can allow only "real" table columns, by comparing that to the actual schema. If you inject any user input into a query string anywhere it must be filtered, PDO, mysqli will not protect you from bad coding practices. Sometimes it is necessary to make these parts of a query dynamic and that is fine as long as the proper precautions are taken. — ArtisticPhoenix, Jul 07 '14 at 01:23
@sectus thank you! the actual problem was my variable declaration. I used $q for the sprintf and $q for the query. There was a conflict (ROFL) — Peter Wateber, Jul 07 '14 at 01:41
@ArtisiticPhoenix You use prepared statements through the API, not a string formatter. — Kermit, Jul 07 '14 at 01:41
Voting to close as this question was caused by a simple typographical error. While similar questions may be on-topic here, this one was resolved in a manner unlikely to help future readers. — Kermit, Jul 07 '14 at 01:42
@kermit - suppose in the above example TABLE_NAME1 = $_POST['table'], I see this a lot people think that because they have mysqli or PDO they are immune to sql attacks then they use sprintf to dynamically change table names from user accessible input. As I said I think those are constants in this case, I just wanted to be sure.. - cheers. — ArtisticPhoenix, Jul 07 '14 at 07:27
@kermit Also, I thought you were asking me about using the place holders ( ie the ? ), my bad on that, I was just warning against using the sprintf() function to build a query.. Which I agree is somewhat pointless with explicit table names, but the op may have some use we don't see in the example. like defining constants for different sites for example, that use similar code base. — ArtisticPhoenix, Jul 07 '14 at 07:33
One last comment this "%?%" is still going to be an issue when using the database api to replace the placeholder, so while the original error was a typo, this one and my answer for it would surely follow. — ArtisticPhoenix, Jul 07 '14 at 07:38

score 3 · Answer 1 · edited May 23 '17 at 12:05

You need only the ? in the query.

$sql = sprintf('SELECT *
                FROM %s
                INNER JOIN %s a ON (a.user_id = created_by)
                LEFT JOIN %s b ON (b.user_id = modified_by)
                WHERE provider_name LIKE ?
                LIMIT ?,?', TABLE_NAME1, TABLE_NAME2, TABLE_NAME3);

then add the wrapping '%''s to the input string.

$q = $this->db->query($sql, array('%'.$q.'%', $page['si'], (int)$offset));

This is the same with using just PDO or mysqli, I use CI ( modified so much I don't know if I would still call it CI ) but not there database implantation.

be careful using sprintf to inject table names into the query string, I take it TABLE_NAME1 are constants, but don't do this with user input without cleaning it first, or you will open yourself up to sql injection attacks.

duplicate of this question

PHP Binding a Wildcard

score 0 · Answer 2 · answered Jul 07 '14 at 01:29

0

My current solution:

concat('%', ?, '%')

@artisticphoenix thank you!

answered Jul 07 '14 at 01:29

Peter Wateber

3,824
4
21
26

sprintf error LIKE %%?%%

2 Answers2