-2

Possible Duplicate:
Best way to stop SQL Injection in PHP
MySQL injection protection and vulnerability signs using PHP

Hey i asked a question about my code if its vulnerable to sql injection The code was this :

$searchData = $_POST['searchData'];

$searchResult = mysql_query("SELECT * FROM songs WHERE songname LIKE '$searchData%' ");

echo $searchResult;

And yess everyone answered that it is ... but i wanted some extra help on how to protect this kind of input from sql injection. I read about mysql_real_escpape_string addslashes etc. But im confues which is the best one ? Shoud i combine them ... or how should i structure my code to protect the input Anyone helpin me with the best solution would be very apercciated.

Community
  • 1
  • 1
aygeta
  • 429
  • 3
  • 7
  • 17

3 Answers3

1

The best approach is not to mess about with escaping at all; use bound parameters. See e.g. the PHP manual on PDO.

An example:

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");

$stuff = array('one', 1);
$stmt->execute($stuff);

$stuff = array('two', 2);
$stmt->execute($stuff);    
?>
Oliver Charlesworth
  • 267,707
  • 33
  • 569
  • 680
  • That's not an illustrative example, it's a detering one. Positional parameters and passing them via `execute(array(..))` are more newbie friendly. (Downvote not mine. Upvote neither.) – mario Sep 07 '11 at 23:27
  • @mario: Fair point. I've modified it to show an example of using unnamed parameters instead. – Oliver Charlesworth Sep 07 '11 at 23:31
0

I highly recommend a short book called Essential PHP Security by Chris Shiflett (by O'Reilly) because you are asking a question that has many aspects that need to be covered. SQL Injection protection starts from the HTML all the way to the database with several steps in between to make sure your data is protected from injection.

Serj Sagan
  • 28,927
  • 17
  • 154
  • 183
0

Short answer: mysql_real_escape_string

Longer answer: prepared statements (using PDO or MySQLi)

Longer-er answer: When possible you should use prepared statements with bound parameters (as Oli Charlesworth said), however, the second best option is to use the escaping mechanism specific to a specific RDBMS. In this case, you would use mysql_real_escape_string instead of addslashes. mysql_real_escape_string is aware of a few things that addslashes is not. addslashes is a glorified str_replace of ' with \'. mysql_real_escape_string is a hook into the MySQL client library's mysql_real_escape_string function. It is aware of a few settings on the server (hence the connection parameter to it), and it is safer than addslashes.

Bound parameters are still safer though. Even though mysql_real_escape_string knows a lot about the connection and so on, a lot of factors can contribute to the security of it. For example, character sets and encodings can be difficult to handle. Bound parameters have a different method of operation, and they do not do escaping like normal queries. As such, they are safer as there is less room for error on things like character encoding and what not.

Corbin
  • 33,060
  • 6
  • 68
  • 78
  • Im not tryin to do who knows what whitthin my application ...im just gettin the input from the box matchin it with the database data and showing the matched results... simply a live search. I guess ill stick with the mysql_real_escpape_string and some regex for html tags and so on. – aygeta Sep 07 '11 at 23:25
  • Bound parameters aren't about doing who knows what within your application. They're about good practices, and the best security possible. Chances are, mysql_real_escape_string will suffice for you need; however, prepared statements can have benefits in other situations, as can using PDO. (I never use RDBMS-specific functions. If I'm not using an ORM, I use PDO.) Anyway, as I said, real escape string will probably suffice, but you should still keep prepared statements in mind. Also, instead of processing HTML with regexps, you should use htmlentities. – Corbin Sep 07 '11 at 23:46
  • (Out of space, so continued...) Or you could use striptags. The only time you should remove HTML instead of just escaping it during output is when you need to mix allowed HTML and non-allowed HTML. (Overly simplified, but typically holds true.) – Corbin Sep 07 '11 at 23:47
  • ill read more about pdo thanks alot – aygeta Sep 07 '11 at 23:50