CODEWITHSUNDEEP
phpmysqlsecurity
user1928545
user1928545

Reputation:

how to securely use LIKE

I'm building a script where users can search a database. my understanding is that PDO doesn't let you set a parameter for the LIKE operand. So I have this code to make up for it

$sQuery = "SELECT * FROM table WHERE column LIKE '%" . $this->sQuery . "%' LIMIT 30";
$Statement = $this->Database->prepare($sQuery);
$Statement->execute();

I doubt this is secure against SQL injection. Is there any way to make it secure?

Upvotes: 1

Views: 58

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562821

You're right, interpolating any value into an SQL string creates a risk for SQL injection vulnerability. It's better to use a SQL query parameter placeholder when you prepare(), and then supply the value as a parameter when you execute().

$pattern = "%" . $this->sQuery . "%";
$sQuery = "SELECT * FROM table WHERE column LIKE ? LIMIT 30";
$Statement = $this->Database->prepare($sQuery);
$Statement->execute(array($pattern));

Take that as pseudocode because I can't tell from your example which MySQL extension you're using. I'm assuming PDO, which allows parameters to be sent as an array argument to execute().

Some people use PDOStatement::bindParam(), but there's no advantage to doing so. Maybe in some other RDBMS brands the PDO::PARAM_STR matters, but in the MySQL driver, the parameter type is ignored.

PS: Aside from the security issue you asked about, you will find a search for wildcard-based patterns like you're doing don't perform well as your data grows larger. See my presentation Full Text Search Throwdown.

Upvotes: 1

helmbert
helmbert

Reputation: 38024

This should work:

$sQuery    = "SELECT * FROM table WHERE column LIKE :query LIMIT 30";
$Statement = $this->Database->prepare($sQuery);
$Statement->execute(array(':query' => '%' . $this->sQuery . '%'));

Upvotes: 0

Related Questions