CODEWITHSUNDEEP
phpmysqlsqlmysqli
Lackie371
Lackie371

Reputation: 41

PHP: Preventing SQL Injection when using LIKE selector

I have a PHP Script that uses a very simple search statement using mysqli. This is the statement :

SELECT * FROM JobTracker WHERE JobName LIKE'%$jobName%'

Now I want to prevent SQL injection but I can't figure out the syntax.

I've tried LIKE '%?%' and LIKE '%' . ? . '%' andLIKE '%' + ? + '%'

but none worked. What is the correct syntax for this SQL statement that will prevent injection?

Upvotes: 0

Views: 73

Answers (2)

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324610

You have two choices:

  • Special characters % and _ should be allowed as wildcards in input:

    ... LIKE CONCAT('%', ?, '%')

  • Wildcard characters should not be allowed:

    ... LIKE ?

$jobName = '%'.str_replace(array("_","%"),array("\\_","\\%"),$jobName).'%';

Upvotes: 1

Mark
Mark

Reputation: 1872

$jobName = real_escape_string($jobName);

$stmt = mysqli->prepare("SELECT * FROM JobTracker WHERE JobName LIKE '%?%'");
$stmt->bind_param("s", $jobName);
$stmt->execute();

PDO statements are really good for preventing SQL injection.

Upvotes: 0

Related Questions