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

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/>";
}

?>

---

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.

---

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/ ?

---

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.

---

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.

---

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.