[RESOLVED] LIKE qualifiers passed in query

[Myth_UK_]

Whats the best way to stop an SQL injection attack when passing LIKE qualifiers, % and _(underscore) to a query? i.e

$name = "%whatever";
$query = sprintf("SELECT name, role FROM usertable WHERE name LIKE $name)

The above is just an example and not ment to be used...

If you passed "%_" as the name in a $POST for example, it would return every record.

To stop this, you could do the following:

$name = "'" . addcslashes(mysql_real_escape_string($name), "%_") . "'";

The above would escape the qualifiers. Or you could just replace the qualifiers with some other characters.

The problem is that both % and _(underscore) are valid input characters. i.e you could have this stored in your database "Joe_Blogs". So escaping the LIKE qualifiers would break the data.

Question: What is the best way to stop injection attacks when using the LIKE?

[Myth_UK_]

I may not have been clear what I was trying to prevent in my previous post, so here is an example...

$name = “%Joe_Blogs"); // Input from form 
mysql_query("SELECT name, role FROM usertable WHERE name LIKE '%{$name}%'");

The LIKE query is not just compromised, but it could result in overloading the database.

At the moment I escape % and _(underscore) on LIKE queries only, and inform the user that wildcards are not permited. But is there a better way?

[johanafm]

mysqli and prepared statements. Or some database abstraction layer like PDO or MDB2

Overloading the database? For most purposes, proper indexing will keep things ok.