1

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.

Improve this question
5
  • 5
    Please take the time to look into using PDO or, at the very least, start using MySQLi. You will do the world a great service :) Commented Apr 20, 2011 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 Commented Apr 20, 2011 at 20:05
  • 1
    It's probably safe. Of course, mysql_escape_string() was also "probably safe" until it was discovered that it wasn't. Commented Apr 20, 2011 at 20:07
  • aaah!! you ruined my fairytale ending. so when did that happen? Commented Apr 20, 2011 at 20:09
  • Oh the possibilities: mysql_escape_stringThis 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. Commented Apr 20, 2011 at 20:35

5 Answers 5

Reset to default
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 }
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

3 Comments

Completely safe? Hex anyone?
SELECT LOAD_FILE(0x633A5C626F6F742E696E69) (M) This will show the content of c:\boot.ini
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.
1

yes it should be fine with mysql_real_escape_string

Improve this answer

3 Comments

thanks! so the way ive set about this is the best practise or is there a better one?
That is the best practice! (or you use prepared statements)
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.
0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.