is this safe in terms of SQL injection?

Question

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.

Please take the time to look into using [`PDO`](http://php.net/pdo) or, at the very least, start using [`MySQLi`](http://php.net/mysqli). You will do the world a great service :) — Kevin Peno, Apr 20 '11 at 20:04
thanks for the links! ive come quite far in the past week through simple tutorials so i'll give those a look in — Ricki, Apr 20 '11 at 20:05
It's probably safe. Of course, `mysql_escape_string()` was also "probably safe" until it was discovered that it wasn't. — Wooble, Apr 20 '11 at 20:07
aaah!! you ruined my fairytale ending. so when did that happen? — Ricki, Apr 20 '11 at 20:09
Oh the possibilities: [`mysql_escape_string`](http://php.net/mysql_escape_string)`This function is identical to mysql_real_escape_string() except that mysql_real_escape_string() takes a connection handler and escapes the string according to the current character set. mysql_escape_string() does not take a connection argument and does not respect the current charset setting.` — Kevin Peno, Apr 20 '11 at 20:35

score 5 · Accepted Answer · answered Apr 20 '11 at 20:04

5

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
}

answered Apr 20 '11 at 20:04

drudge

35,471
7
34
45

score 3 · Answer 2 · answered Apr 20 '11 at 20:04

3

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.

answered Apr 20 '11 at 20:04

Hammerite

21,755
6
70
91

Completely safe? Hex anyone? – k to the z Apr 20 '11 at 20:07
SELECT LOAD_FILE(0x633A5C626F6F742E696E69) (M) This will show the content of c:\boot.ini – k to the z Apr 20 '11 at 20:22
I don't see how you would get that query to execute using the posted code. Please elaborate as to how your example query is relevant. – Hammerite Apr 21 '11 at 00:47

score 1 · Answer 3 · answered Apr 20 '11 at 20:03

1

yes it should be fine with mysql_real_escape_string

answered Apr 20 '11 at 20:03

Daniel A. White

187,200
47
362
445

thanks! so the way ive set about this is the best practise or is there a better one? – Ricki Apr 20 '11 at 20:04
That is the best practice! (or you use prepared statements) – Tim Apr 20 '11 at 20:04
It's definitely not "best practice". I strongly advise using an abstract database model, or at least a library like `MySQLi` (as mentioned and linked in Kevin Peno's comment) to handle this. – Henry Merriam Apr 20 '11 at 20:08

score 0 · Answer 4 · answered Apr 20 '11 at 20:27

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.

score 0 · Answer 5 · answered Apr 20 '11 at 20:29

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.

is this safe in terms of SQL injection?

5 Answers5

Linked