Currently getting more and more into MySQL. It's something i haven't been too fussed about but i want to write some scripts with it now.
My question is simple, im making a search script and just want to know if my php code can prevent some SQL injections.. the code:
$orig = $_POST['term'];
$term = mysql_real_escape_string($orig);
$sql = mysql_query("select * from db1 where content like '%$term%' ");
Is this ok? Alternatively if anyone has an easier/better/safer way of doing this plese feel inclined to let me know.
To avoid warnings in case $_POST['term'] isn't set:
if (isset($_POST['term'])) {
$term = mysql_real_escape_string($_POST['term']);
$sql = mysql_query("select * from db1 where content like '%$term%' ");
// rest of sql query
}
Yes, it is safe from SQL injection. If you want to use a more systematic method of avoiding SQL injection issues I would recommend learning to use PDO and parameterised queries.
yes it should be fine with mysql_real_escape_string
The standard escaping is often insufficient for values used in the LIKE clause. Unless you want the user to specify % placeholders of his own, you should add:
$term = mysql_real_escape_string($_POST['term']);
$term = addcslashes($term, "%_");
To be precise, this only an issue for very large tables, where excessive %%%% placeholder injection in LIKE queries could decelerate the database server.
In your case mysql_real_escape_string will prevent SQL injection because it escapse single quotes and your string is set between single quotes. So in any case $term will always be just a simple string for SQL.
If you have something like
select * from A where id = $number
then no escaping would prevent an injection like:
0; drop A;
To prevent this scenario you would go well with prepared statements (PDO) or type-checking.
