Is this a 100% safe from SQL Injection and will word correctly for any input?

Question

$a=$_GET["a"];
$b=$_GET["b"];
$a=str_replace("%", "\%", $a);
$b=str_replace("%", "\%", $b);
$sql="SELECT * FROM table ";
$sql.="WHERE ColA LIKE :txtA AND ColB LIKE :txtB";
$query = $db->prepare($sql);
$query->bindValue(':txtA', '%'.$a.'%', PDO::PARAM_STR);
$query->bindValue(':txtB', '%'.$b.'%', PDO::PARAM_STR);
$query->execute();

I want to give user ability to search anything. Should I filter more characters? Will it be 100% safe and work as it should do for searching pieces of text in database?

@MadhurBhaiya I need if it's "LIKE" because user can use "%" and i want use "%" too — Steve143, Sep 25 '18 at 14:18
I'm voting to close this question as off-topic because I feel it should be posted in code review https://codereview.stackexchange.com — Funk Forty Niner, Sep 25 '18 at 14:22
[Similar question](https://stackoverflow.com/questions/18877098/pdo-search-database-using-like) — Alex W, Sep 25 '18 at 16:11

Bill Karwin · Accepted Answer · 2018-09-25T16:22:55.127

Yes, your code follows good practices for SQL injection prevention. Using parameters is the right way to do it.

The only suggestion would be optional, for the sake of code style, not secure code. Yours is just as secure.

$a=preg_replace('/[%_]/', '\\\\$0', $_GET["a"]);
$b=preg_replace('/[%_]/', '\\\\$0', $_GET["b"]);
$sql="
    SELECT * FROM table 
    WHERE ColA LIKE :txtA AND ColB LIKE :txtB";
$query = $db->prepare($sql);
$query->execute(['txtA'=>"%{$a}%", 'txtB'=>"%{$b}%"]);

PDO allows you to pass query parameters as an array argument to execute(), so you don't have to use bindValue().

Also, you don't need PDO::PARAM_STR arguments to bindValue() at least not for MySQL. The PDO driver for MySQL always passes parameters as strings. Maybe it's required for some other brands of database.

You can write your SQL query in one multi-line string (unlike some languages like Java). You don't need to use .=.

You can embed PHP variables inside double-quoted strings. You don't need to use '%'.$a.'%', you can use "%{$a}%". I find the latter a little more more readable, especially when you have multiple variables to include.

I forgot here "_". If user put this character then everything can change. Thank you. — Steve143, Sep 25 '18 at 16:16
I updated the first two lines to use `preg_replace()` so it would escape `'_'` characters. — Bill Karwin, Sep 25 '18 at 16:23

Is this a 100% safe from SQL Injection and will word correctly for any input?

1 Answers1