1

I'm doing a live search with jQuery and PHP but I'm not sure if my query is vulnerable to SQL injection.

The data is posted into a PHP file while typing characters with the help of jQuery.

$searchData = $_POST['searchData']; 

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

echo $searchResult;

Is this vulnerable to SQL injection?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
aygeta
  • 429
  • 3
  • 7
  • 17

7 Answers7

9

Yes, consider if $searchData is:

Robert'); DROP TABLE songs; --
Oliver Charlesworth
  • 267,707
  • 33
  • 569
  • 680
  • wow this is dangerous... ill try to avoid do somthing with those functions mysql_ereg_replace ... but if you have any safer solution aboout my problem i would be very happy to see it – aygeta Sep 06 '11 at 00:26
  • 1
    Note that that particular example will not work, because the mysql_ lib does not allow executing 2 queries in one statement. As long as you keep the manipulation in a single statement the injection will work. (source: http://stackoverflow.com/q/60174/431967) – Highmastdon Jul 12 '13 at 13:04
1

use mysql_real_escape_string($_POST['searchData']), or [PDO] instead mysql_*

p.campbell
  • 98,673
  • 67
  • 256
  • 322
steve
  • 608
  • 1
  • 5
  • 16
0

If $searchData isn't being escaped anywhere, then yes, it's vulnerable.

icktoofay
  • 126,289
  • 21
  • 250
  • 231
0

Anytime you are taking user input and putting into a query, you should pass it through mysql_real_escape_string. Better safe than sorry.

p.campbell
  • 98,673
  • 67
  • 256
  • 322
frostmatthew
  • 3,260
  • 4
  • 40
  • 50
  • mysql_real_escape_string has nothing to do with user input. and even from user input it can save only strings – Your Common Sense Sep 23 '11 at 12:45
  • @Col. Shrapnel how do you figure it has nothing to do with user input? http://php.net/manual/en/function.mysql-real-escape-string.php according to the powers that be it should "be used to make data safe before sending a query to MySQL" - is that not exactly what the OP asked for? Preventing a sql injection? Does my answer not achieve this? – frostmatthew Sep 23 '11 at 16:31
  • `data for sending a query to MySQL` is NOT equal to user input. your answer only partial and rather misleading. not only user input should be escaped, yes numbers and identifiers cannot be protected by this function – Your Common Sense Sep 23 '11 at 16:55
0

Since you can use tools to manipulate sent $_POST data, yes, it is dangerous.

Either you escape it or use php data objects (PDO) which doesn't need ANY escaping and is what you should use anyway in this time.

p.campbell
  • 98,673
  • 67
  • 256
  • 322
omasdg
  • 116
  • 3
  • PDO is no magic bullet and should not be portrayed as one. It provides no advantage without strictly using bound parameters only. – mario Sep 06 '11 at 00:45
  • mario is correct, parametrized queries render sql injections useless – omasdg Sep 06 '11 at 10:46
0

Yes, it is vulnerable. But other responders failed to note that along with the normal escaping (like mysql_real_escape_string()) you also need to escape the % character for LIKE clause!

mysql_real_escape_string(addcslashes($str, "%_"))

The trick to grasp here is that there is nothing like "universal quoting". When quoting, you always quote text for some particular output, like:

  1. string value for mysql query
  2. like expression for mysql query
  3. html code
  4. json
  5. mysql regular expression
  6. php regular expression

For each case, you need different quoting, because each usage is present within different syntax context. This also implies that the quoting shouldn't be made at the input into PHP, but at the particular output! Which is the reason why features like magic_quotes_gpc are broken (never forget to handle it, or better, assure it is switched off!!!).

So, what methods would one use for quoting in these particular cases? (Feel free to correct me, there might be more modern methods, but these are working for me)

  1. mysql_real_escape_string($str)
  2. mysql_real_escape_string(addcslashes($str, "%_"))
  3. htmlspecialchars($str)
  4. json_encode() - only for utf8! I use my function for iso-8859-2
  5. mysql_real_escape_string(addcslashes($str, '^.[]$()|*+?{}')) - you cannot use preg_quote in this case because backslash would be escaped two times!
  6. preg_quote()
Tomas
  • 57,621
  • 49
  • 238
  • 373
  • hey, may be you'd limit yourself with one or two answers and then merely point to them in comments? It seems you're going to spam every injection-related question with your 6-point escaping mantra – Your Common Sense Sep 23 '11 at 12:25
  • @Col.Sh, you call answering question "spamming"? Was this reason for downvoting? – Tomas Sep 23 '11 at 12:27
  • @Col. Shrapnel, but ok, next time I can put the link. – Tomas Sep 23 '11 at 12:29
-1

Yes You can use this :

$searchData = addslashes( $_POST['searchData'] );
DJafari
  • 12,955
  • 8
  • 43
  • 65