mysql_real_escape_string and htmlspecialchars while matching

[DarthGuido]

@cpradio

Not necessarily. As the product_id=50 is likely going to be user initiated.

You will likely have URLs like so:
mydomain.com/product.php?product_id=50

Then your query would be similar to

$qry = "select * from product_table where product_id=" . mysql_real_escape_string($_REQUEST['product_id']);

Granted, a even more secure query would be

$product_id = intval($_REQUEST['product_id']);
$qry = "select * from product_table where product_id=" . $product_id;

When you use the user input value in the query without quotes around it, then using mysql_real_escape_string gives no security at all. In this case, the only secure way (apart from using prepared statements) would be to make sure the product id contains a valid numeric value (like you do in your second query).

If $_REQUEST[‘product_id’] would contain ‘1 OR 1=1’ then your first query would become:

select * from product_table where product_id=1 OR 1=1

[cpradio]

@vinpkl

hi cp

i created a new page “injection.php”

it contains a query without mysql_real_escape_string

<?
require_once("connection.php"); 
$qry = "select * from productstable where id=".$_REQUEST['id'];
$result = mysql_query($qry);
$row = mysql_fetch_array($result);
echo $row['id'];
echo "<br>";
echo $row['name'];
?>

then i created a fake table “phone_covers”

then i tried to add sql injection via writing in the addressbar

http://localhost/site/injection.php?product_id='drop table phone_covers'

but that phone_covers table is not getting dropped/deleted.

vineet

You need to alter your query string, it would be more so: product_id=1;DROP TABLE phone_covers; but that might not work either, as I can’t remember if MySQL permits the use of ; to separate multiple SQL statements.

However, as @guido2004 ; pointed out, you can easily get it to return more than 1 product by altering the query string to product_id=50 OR product_id%3D3

@guido2004

When you use the user input value in the query without quotes around it, then using mysql_real_escape_string gives no security at all. In this case, the only secure way (apart from using prepared statements) would be to make sure the product id contains a valid numeric value (like you do in your second query).

If $_REQUEST[‘product_id’] would contain ‘1 OR 1=1’ then your first query would become:

select * from product_table where product_id=1 OR 1=1

Good catch, I did miss that.

[vinpkl]

@guido2004

When you use the user input value in the query without quotes around it, then using mysql_real_escape_string gives no security at all. In this case, the only secure way (apart from using prepared statements) would be to make sure the product id contains a valid numeric value (like you do in your second query).

If $_REQUEST[‘product_id’] would contain ‘1 OR 1=1’ then your first query would become:

select * from product_table where product_id=1 OR 1=1

hi guido

If i use quotes around mysql_real_escape_string for product id then ‘product_id’ will accept any string and it will be harmful.

And without quotes mysql_real_escape_string is again not useful

qry2 = "select * from product_table where product_id='" . mysql_real_escape_string($_REQUEST['product_id'])."'"; 

This is what you were try to explain to me

vineet

[DarthGuido]

@vinpkl

hi guido

If i use quotes around mysql_real_escape_string for product id then ‘product_id’ will accept any string and it will be harmful.

And without quotes mysql_real_escape_string is again not useful

qry2 = "select * from product_table where product_id='" . mysql_real_escape_string($_REQUEST['product_id'])."'"; 

This is what you were try to explain to me

vineet

That query isn’t harmful. If I put ‘1 OR 1=1’ in $_REQUEST[‘product_id’], that query just won’t return any rows, and that is right.

[vinpkl]

@cpradio

You need to alter your query string, it would be more so: product_id=1;DROP TABLE phone_covers; but that might not work either, as I can’t remember if MySQL permits the use of ; to separate multiple SQL statements.

hi cp

i tried below code but it didnt dropped the table

http://localhost/site/injection.php?product_id=1;DROP TABLE phone_covers;

Is it something to do with mysql or php version

vineet

[vinpkl]

@cpradio

$product_id = intval($_REQUEST['product_id']);
$qry = "select * from product_table where product_id=" . $product_id;

hi cp

Also while displaying the product_id i will be using intval or htmlspecialchars

<?
echo intval($product_id);
?>

vineet

[cpradio]

@vinpkl

hi cp

Also while displaying the product_id i will be using intval or htmlspecialchars

<?
echo intval($product_id);
?>

vineet

It doesn’t matter for an integer based value, however, if you want to always display a 0 or the actual number, intval() will be better suited. As htmlspecialchars will let it display non-numeric values (if product_id were to somehow contain non-numeric values).

[vinpkl]

hi cp

Does htaccess url Shortening/rewriting helps in avoiding sql injection

vineet

[cpradio]

No, as that data is simply passed back to your script.

[vinpkl]

hi cp

If i am already having mysql_real_escape_string in my query.

Then is it required to add mysql_real_escape_string
to the
variable values of the <a href> url link also

<a href=products.php?dealerid=1&dealername='samsung'> 

Do i need to convert the above link to

<a href=products.php?dealerid=".intval(1)."&dealername=".mysql_real_escape_string('samsung')."> 

EDIT : You already posted in your earlier post that mysql_real_escape_string is used only with where clause in query.

But still wanted to confirm.

vineet

[cpradio]

@vinpkl

hi cp

If i am already having mysql_real_escape_string in my query.

Then is it required to add mysql_real_escape_string
to the
variable values of the <a href> url link also

<a href=products.php?dealerid=1&dealername='samsung'> 

Do i need to convert the above link to

<a href=products.php?dealerid=".intval(1)."&dealername=".mysql_real_escape_string('samsung')."> 

EDIT : You already posted in your earlier post that mysql_real_escape_string is used only with where clause in query.

But still wanted to confirm.

vineet

No, but you may want to use urlencode() so that any characters such as & or = are converted to their hexidecimal format.

[vinpkl]

hi cp

what do we use to check whether value of intval is set or not

$id=intval($_REQUEST['id']);

if(!isset($id)

or

if($id == 0)

vineet

[cpradio]

@vinpkl

hi cp

what do we use to check whether value of intval is set or not

$id=intval($_REQUEST['id']);

if(!isset($id)

or

if($id == 0)

vineet

$id = 0;
if (isset($_REQUEST['id']) && is_numeric($_REQUEST['id']))
{
  //valid int
  $id = intval($_REQUEST['id']); 
}

[vinpkl]

@cpradio

$id = 0;
if (isset($_REQUEST['id']) && is_numeric($_REQUEST['id']))
{
  //valid int
  $id = intval($_REQUEST['id']); 
}

hi cp

If $id is not set then it will always return “0” instead of NULL

vineet

[cpradio]

With the way I wrote it, yes that is true. You can default it to null if you’d like.