0

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?

Praveen Gowda I V
  • 9,569
  • 4
  • 41
  • 49
Dave
  • 253
  • 6
  • 14
  • Using prepared statement can help. Otherwise you need to fetch and change the value of the keywork, like change ' with \' and " with \" – Marco Mura Feb 27 '15 at 08:39
  • possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – giorgio Feb 27 '15 at 09:01

1 Answers1

2

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
iswinky
  • 1,951
  • 3
  • 16
  • 47
  • This is the way to go. – STT LCU Feb 27 '15 at 09:16
  • Interesting! How does PDO handle the wierd characters? Do i need to handle $searchedKeyord externaly before putting it in the query or is it solved automagicly? Thanks for your answer mate, this looks very interesting. – Dave Feb 27 '15 at 09:55
  • `prepare` pretty much does the same as `mysql_real_escape_string` (deprecated). However it's still recommended to filter input and sanitize data before `inserting` into a database. – iswinky Feb 27 '15 at 10:09