如何防止奇怪的用户输入破坏查询中的内容？

Question

Is there a good standard solution to deal with characters like ' and " from being used in user inputs on a web platform?

I'm using php for a webpage and if I have, for example, a search bar which have the following query behind it.

$sql = "select * from aTable where aColumn like '%".$searchedKeyword."%'";

If I search for like Greg's icecream the ' will break the script. Also, I'm guessing if I search for something like 1' or ID>0 my script will have a false effect.

What is the common solution here? Do you usually filter away undesired characters, or is there maybe some method or similiar built-in to php?

Answer 1

You can us PDO and prepared statements to prevent SQL injection.

http://php.net/manual/en/pdo.prepared-statements.php

$searchedKeyword = "mykeyword";

//Database details
$db = 'testdb';
$username = 'username';
$password = 'password';

//Connect to database using PDO (mysql)
try {
    $dbh = new PDO('mysql:host=localhost;dbname='.$db, $username, $password);
} catch (PDOException $e) {
    var_dump("error: $e");
}

//Prepared SQL with placeholder ":searchedKeyword"
$sql = "select * from aTable where aColumn like '%:searchedKeyword%'";
$sth = $dbh->prepare($sql);

//Bind parameter ":searchedKeyword" to variable $searchedKeyword
$sth->bindParam(':searchedKeyword', $searchedKeyword);

//Execute query
$sth->execute();

//Get results
$result = $sth->fetchAll(); //fetches all results where there's a match