Is this a 100% safe from SQL Injection and will work correctly for any input? [closed]

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?

Edit: ColA and ColB are strings, just text (phrase and tags in search engine)

Answer 1

Security-wise your code is OK. There is not a single variable put inside your query string - so you can tell that it's 100% safe.

Regarding the search quality, like you rightfully noted in the comment, there is also a "_" wildcard character.

Besides, as it is noted in the mysql manual, a backslash character must be escaped with three slashes as well.

So for the bullet-proof escaping for LIKE you could come up with a function

function escapeForLike($str) {
    $str = str_replace("\\", "\\\\\\\\", $str);
    $str = str_replace("%", "\%", $str);
    $str = str_replace("_", "\_", $str);
    return $str;
}

Using this function and some improvements that PDO offers, you can make your code like this

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

Answer 2

I want to give user ability to search anything.

It looks like you've achieved that, however, I'd use $_POST to reduce the likelihood that your application will be taken offline by users opening multiple tabs or search engine spiders hitting URLs.

Will it be 100% safe and work as it should do for searching pieces of text in database?

Looks good for SQL injection.

As to whether it "should do" - have you considered generating the query dynamically to allow for tokenized search on inputs like "foo bar"? (i.e. ColB LIKE :token1 OR ColB LIKE :token2)