40' OR '1'='1 this is prone to sql injection?

Question

I have developed a simple application for study of sql injection where i search for rate < 40 and retrive all the names having rate less than 40 but when i pass search as 40' OR '1'='1 so it retrieves all the records from database table I know how to solve this problem but i don't know 40' OR '1'='1 how this statement works when i pass 40' OR '1'='1 can any one briefly tell me what happens when i pass 40' OR '1'='1 in search box ??

hoping for quick and positive response ...

<?php
include("conn.php");

$get_rate = $_GET['rate'];

$query = "select * from `sqlinjection`.`products` WHERE `products`.`rate` < '".$get_rate."'";


$result=mysql_query($query);

if($result == false)
{
    die(mysql_error());

}

while($row=mysql_fetch_array($result))
{

echo "Name".$row['srno']."<br/>";
echo "Name".$row['name']."<br/>";
echo "Rate".$row['rate']."<br/>";
}

?>

You can format source code with the `010110` toolbar button. I've done it for you this time. — Álvaro González, Dec 23 '10 at 10:50
@Alvaro - I was going to edit that but I'm minus 50 off reputation :-( — m.edmondson, Dec 23 '10 at 10:55
@Alvaro yeh i always had problem while posting source code. where is the option for 010110 .. how to format source code ? — Vishwanath Dalvi, Dec 23 '10 at 10:56
@mr_eclair: The image was changed recently, it now looks like this: `{}` — Piskvor left the building, Dec 23 '10 at 10:58
@Piskvor yeh thanks a lot .. now i got it how to format source code.. thank u all .. for sharing ur awesome knowledge.. and providing best solution for my doubt. — Vishwanath Dalvi, Dec 23 '10 at 11:03
@Piskvor: oops, I hadn't noticed neither the new icon nor the `
` and `` tags. I'm used to just hit `Ctrl+K` <:-) — Álvaro González, Dec 23 '10 at 11:10
A complete guide on SQL injecton protection: http://stackoverflow.com/questions/2993027/in-php-when-submitting-strings-to-the-db-should-i-take-care-of-illegal-characters/2995163#2995163 — Your Common Sense, Dec 23 '10 at 12:42

m.edmondson · Accepted Answer · 2010-12-23T12:44:40.527

5

This is exactly how SQL Injection works

Imagine the query to the database. Usually it would be something like this:

select *
from products
where productID = '[some parameter]'

but in your case since the parameter has come from the user, and that user entered 40' OR '1'='1 you're left with:

select *
from products
where productID = '40' OR '1'='1'

as 1 = 1 it will select everything

The best way to prevent this is to use a parametrised query. Excellent article.

edited Dec 23 '10 at 12:44

answered Dec 23 '10 at 10:41

m.edmondson

30,382
27
123
206

yes i'm learning .. how to secure web application from sql injection but how this query works i hv doubt in that – Vishwanath Dalvi Dec 23 '10 at 10:42
Because normally you will have this productID = '[some parameter]'. If I replace '[some parameter]' with 40' OR '1'='1 I'm left with productID = '40' OR '1'='1' – m.edmondson Dec 23 '10 at 10:47
`a parametrised query such as Stored Procedures` are you sure you do quite understand what did you say? – Your Common Sense Dec 23 '10 at 12:40
Sorry I meant use a parametrised query, forget the stored procedures bit. I think I mind was jumping ahead of what I was typing. – m.edmondson Dec 23 '10 at 12:44

score 3 · Answer 2 · answered Dec 23 '10 at 10:44

Think about it in terms of what the resulting SQL query will look like:

SELECT * FROM my_table WHERE rate<'40' OR '1'='1';

'1' does indeed equal '1' so rate<'40' OR '1'='1' will always be true, and every record will be returned. Have you seen this before: http://xkcd.com/327/ ?

score 2 · Answer 3 · answered Dec 23 '10 at 10:48

Try modifying your script by adding some debug information:

$get_rate = $_GET['rate'];
$query = "select * from sqlinjection.products WHERE products.rate < '".$get_rate."'";
echo $query;

You should see that resulting SQL is:

select * from sqlinjection.products WHERE products.rate < '20' OR '1'='1'
                                                           ^^^^^^^^^^^^^

The quote inside the value of $get_rate terminates the string '20', so the following OR '1'='1' is interpreted as SQL. Since '1' = '1' is always true, this query returns all rows.

@Marks +1 thats better.. to learn how it works by actually passing the sqlinjection cheat code .. through query.. — Vishwanath Dalvi, Dec 23 '10 at 11:05

Piskvor left the building · Answer 4 · 2010-12-23T10:56:01.883

You seem to have an application that ultimately tries this:

SELECT somefield FROM sometable WHERE rate < '$yourRate'

which would normally (e.g. with 40 as the input) get expanded to

SELECT somefield FROM sometable WHERE rate < '40'

However, if you don't sanitize your SQL input, and a malicious user enters 40' OR '1'='1, the query becomes

SELECT somefield FROM sometable WHERE rate < '40' OR '1'='1'

and since `'1'='1' is always true, it is equivalent to

SELECT somefield FROM sometable

Now, what to do? If the app is still small, I'd suggest switching to mysqli - it allows you to use Prepared Statements, which are much more resilient against this. If not, at the very least sanitize your parameters. That means, if there's supposed to be an integer as a parameter, cast the input to integer; if a string, use the language's escape function - in PHP, this would be mysql_real_escape_string. Example in PHP:

$get_count = $_GET['count']; // an example of integer
$get_rate = $_GET['rate'];   // an example of float
$comment = $_GET['comment']; // an example of string

$sanitized_count = (int) $get_count; // now we're *sure* it's an int, and nothing else
$sanitized_rate = (float) $get_rate; // ditto
$sql_sanitized_comment = mysql_real_escape_string($comment); // you need to have the mySQL connection open to use this function (for charset purposes)

$query = "select * from sqlinjection.products 
            WHERE 
              products.rate < ".$sanitized_rate."
              OR products.comment = '" . $sql_sanitized_comment . "'
            LIMIT " . $sanitized_count;

This way, the parameters are safe from SQL injection.

score 1 · Answer 5 · answered Dec 23 '10 at 10:45

1

Basically what happens is the statement

SELECT * FROM blah WHERE rate > 'param'

gets passed 40' OR '1'='1 which becomes

SELECT * FROM blah WHERE rate > '40' OR '1'='1

Since 1 = 1 for all rows all the rows are returned.

answered Dec 23 '10 at 10:45

Jim

22,354
6
52
80

40' OR '1'='1 this is prone to sql injection?

5 Answers5