6

Possible Duplicate:
Best way to prevent SQL injection in PHP?

Ive been doing a bit of testing to protect my sites from SQL Injection. I see there are a couple of ways of doing so, Escaping my user inputs, adding slashes, or better yet using parameterized sql statements.

I had this test code..

$q=$_GET["q"];
$game = mysql_query("SELECT * FROM `Games` WHERE `id` = '$q'");
$game = mysql_fetch_array($game);

echo "<h4>ID: ".$game[0]."<br /></h4>name: " . $game[1];

And I tried several SQLi requests and could not get my test page to error, or show any extra data.

But when i changed my sql statement code to this (Removed the single quotes around $q)..

$game = mysql_query("SELECT * FROM `Games` WHERE `id` = $q");

I could perform simple SQLi's and get some results.

So is just wrapping my user inputs in single quotes good enough? Or have i over looked more complex SQLi techniques?

Community
  • 1
  • 1
user1741863
  • 63
  • 4
  • 3
    Use PDO with prepared statement. That's the safer option as of now in the year 2012. – itachi Oct 12 '12 at 17:13
  • 1
    check if your php server have `magic_quotes_gpc=ON` that will help to prevent SQL injection – jcho360 Oct 12 '12 at 17:14
  • Use prepared statements (mysqli or POD). Saves a lot of hassle and mysql is deprecated. – Ed Heal Oct 12 '12 at 17:15
  • 1
    @jcho360: magic quote? Seriously? – itachi Oct 12 '12 at 17:16
  • You're missing the point that even if you try hard, you might have overseen something. So it's practically more useful, to take a library that takes care of that and is written and reviewed by more programmers than just you. See the related question I just linked. – hakre Oct 12 '12 at 17:20
  • @itachi http://aaroncameron.net/article.html?aID=59 why been sarcasm? – jcho360 Oct 12 '12 at 18:07
  • @jcho360 magic_quotes does not stop sql injection. it has vulnerabilities excluding all other problems it created. – itachi Oct 12 '12 at 18:15
  • @itachi http://stackoverflow.com/questions/2589820/is-it-necessary-to-use-mysql-real-escape-string-when-magic-quotes-gpc-is-on it could help – jcho360 Oct 12 '12 at 18:23

3 Answers3

3

You are mentioning SQL injection and parameterized statements, but does not seem to be across PDO? - Use PDO http://www.php.net/manual/en/pdo.prepare.php

dakdad
  • 2,947
  • 2
  • 22
  • 21
1

you should use mysql_real_escape_string() function

In this case you have to use the single quotes

 $game = mysql_query("SELECT * FROM `Games` WHERE `id` = '$q'");

let suppose user input is 1' than the escape_string output will be 1\'

if you are not using quotes than the query will be

SELECT * FROM `Games` WHERE `id` = 1'

//that is wrong or a injection

But in the single quotes case

SELECT * FROM `Games` WHERE `id` = '1\''

that is good and can remove the sql-injection

StaticVariable
  • 5,253
  • 4
  • 23
  • 45
  • 1
    `mysql_*` are deprecated and will be remove in future release. – itachi Oct 12 '12 at 17:17
  • @itachi don't be oversmart.See the question properly `properly..Escaping my user inputs, adding slashes, or better yet using parameterized sql statements....` – StaticVariable Oct 12 '12 at 17:21
  • Negative vote has nothing to do with the question but the answer. Isn't it suppose to be the way it is? You use a function which is deprecated already. Hence this answer lacks the integrity in my eyes and hence the downvote. – itachi Oct 12 '12 at 17:26
  • @itachi than i think you should check your eyes properly... – StaticVariable Oct 12 '12 at 17:28
  • This method is still working and is the best way so far.dont make negative votes for this user.do it when that function is removed – Abadis Oct 14 '12 at 20:04
1

Try this input:

abc' OR id <> '

it will lead to following statement:

"SELECT * FROM `Games` WHERE `id` = 'abc' OR id <> ''"

That would return all games instead of only one. If your page allows to show the whole result, then we would definitely see too much...

The way out is to use PDO with prepared statements, are at least the mysqli_real_escape_string() function before inserting the user input into the SQL statement.

SQL-Injection can do a lot more, in the worst case you can even get control over the server. Have a look at this SQL Injection Cheat Sheet

martinstoeckli
  • 23,430
  • 6
  • 56
  • 87
  • Hi, i just tried that input and nothing was returned. I tried lots similar to this and got the same result, hence me asking if single quotes are enough. Could it be the `magic_quotes_gpc=ON` that @jcho360 mentioned stopping these attacks? – user1741863 Oct 12 '12 at 17:55
  • 1
    @user1741863 - That's possible, you can check it very easily, just make a php page calling `phpinfo();` and search for magic_quotes_gpc. I had to do a presentation and created a page where you can [play with SQL-Injections](http://www.martinstoeckli.ch/hash/hash_sqlinjection.php?step=2), unfortunately it's in german. – martinstoeckli Oct 12 '12 at 18:03
  • indeed it is on. Thanks for the page, google translate to the rescue. – user1741863 Oct 12 '12 at 18:42