mysql_real_escape_string and htmlspecialchars while matching

**vinpkl** · Oct 15, 2012 05:48

hi all

Do we use mysql_real_escape_string and htmlspecialchars while matching the values from database

PHP Code:


<?

$user_id = mysql_real_escape_string($_POST['user_id']);



if($user_id = $row['user_id']

{

    /* do something */

}

?>

or

PHP Code:


<?

$user_id = mysql_real_escape_string($_POST['user_id']);



if($user_id == mysql_real_escape_string($row['user_id'])

{

    /* do something */

}

?>

or

PHP Code:


<?

$user_id = mysql_real_escape_string($_POST['user_id']);



if($user_id == htmlspecialchars($row['user_id'])

{

    /* do something */

}

?>

vineet

**cpradio** · Oct 15, 2012 05:57

None of the above, unless you are doing your matching within your Query (then you would only use mysql_real_escape_string).

Example:

PHP Code:


$result = mysql_query("SELECT * FROM users WHERE user_id = " . mysql_real_escape_string($_POST['user_id']));
if (mysql_num_rows($result) != 1)
{
  // user_id does not exist in table users
}
else
{
  // user does exist, read the record
  $row = mysql_fetch_assoc($result);
}

**guido2004** · Oct 15, 2012 06:04

mysql_real_escape_string is used before you use user data in a query (to prevent sql injection)
htmlspecialchars is used before outputting data to the client.

Like already explained to you in this post.

Instead of using the mysql extension, you might want to look into mysqli or pdo (google for it...).

**vinpkl** · Oct 15, 2012 06:09

ok thanks for the replies

Do we use htmlspecialchars while outputting values in emails

vineet

**cpradio** · Oct 15, 2012 06:11

Yes, if you are building HTML emails, No, if you are using Plain Text emails

**vinpkl** · Oct 15, 2012 06:55

hi cp

while one would be better/safe as using htmlspecialchars for email content between the 2 codes below

PHP Code:


 $body="

<html>

<head></head>

<body>

<table>

     <tr>

    <td>Hi ". htmlspecialchars($name).",<br>

      <br>

      City = " . htmlspecialchars($city). "<br>

     Thanks<br>  

    </td>

  </tr>

</table>

</body>

</html>";

mail($to,$subject,$body,$headers)

PHP Code:


$body="

<html>

<head></head>

<body>

<table>

     <tr>

    <td>Hi ". $name.",<br>

      <br>

       City = " .$city. "<br>

     Thanks<br>  

    </td>

  </tr>

</table>

</body>

</html>";

mail(htmlspecialchars($to),$subject,htmlspecialchars($body),$headers)

vineet

**cpradio** · Oct 15, 2012 07:06

Originally Posted by vinpkl

hi cp

while one would be better/safe as using htmlspecialchars for email content between the 2 codes below

PHP Code:


 $body="
<html>
<head></head>
<body>
<table>
     <tr>
    <td>Hi ". htmlspecialchars($name).",<br>
      <br>
      City = " . htmlspecialchars($city). "<br>
     Thanks<br>  
    </td>
  </tr>
</table>
</body>
</html>";
mail($to,$subject,$body,$headers)

PHP Code:


$body="
<html>
<head></head>
<body>
<table>
     <tr>
    <td>Hi ". $name.",<br>
      <br>
       City = " .$city. "<br>
     Thanks<br>  
    </td>
  </tr>
</table>
</body>
</html>";
mail(htmlspecialchars($to),$subject,htmlspecialchars($body),$headers)

vineet

You can't use your second example for many reasons.

You don't want to run htmlspecialchars on your $to field, that doesn't make sense and you may make what is a valid email address, invalid.
By running htmlspecialchars on the entire body, you are making your HTML content unusable. As it will convert ALL of the HTML tags to be < and >

So with that said, your first example is the way to go.

**vinpkl** · Oct 15, 2012 09:00

hi cp

This code works fine

PHP Code:


if(mysql_real_escape_string(isset($_REQUEST['id'])))

But This code gives error

PHP Code:


if(isset(mysql_real_escape_string($_REQUEST['id'])))



Fatal error: Can't use function return value in write context in

why cant isset come first

vineet

**cpradio** · Oct 15, 2012 09:10

Well, technically speaking both are wrong.

The first example you provide (it is my understanding) would always return true, even if the $_REQUEST['id'] wasn't set. Because mysql_real_escape_string would be evaluating true or false (the return value of isset).

The second example is wrong because you are running isset() on mysql_real_escape_string() which evaluations the $_REQUEST['id'] before knowing if it is set or not.

So how should you be evaluating this? Like so:

PHP Code:


if (isset($_REQUEST['id']))
{
  $id = mysql_real_escape_string($_REQUEST['id']);
}

Keep in mind, that isset() does not try to utilize the value of the request, but rather just checks if it exists or not. It is only when you need to access the value and use it in a query that you need to use mysql_real_escape_string

**felgall** · Oct 15, 2012 14:46

Originally Posted by vinpkl

hi cp

while one would be better/safe as using htmlspecialchars for email content between the 2 codes below

PHP Code:


 $body="
<html>
<head></head>
<body>
<table>
     <tr>
    <td>Hi ". htmlspecialchars($name).",<br>
      <br>
      City = " . htmlspecialchars($city). "<br>
     Thanks<br>  
    </td>
  </tr>
</table>
</body>
</html>";
mail($to,$subject,$body,$headers)

PHP Code:


$body="
<html>
<head></head>
<body>
<table>
     <tr>
    <td>Hi ". $name.",<br>
      <br>
       City = " .$city. "<br>
     Thanks<br>  
    </td>
  </tr>
</table>
</body>
</html>";
mail(htmlspecialchars($to),$subject,htmlspecialchars($body),$headers)

vineet

Neither name nor city ought to contain any < or & characters and so escaping either field to use them in HTML provided that they were properly validated in the first place should be unnecessary.

**cpradio** · Oct 15, 2012 14:53

Originally Posted by felgall

Neither name nor city ought to contain any < or & characters and so escaping either field to use them in HTML provided that they were properly validated in the first place should be unnecessary.

< and & are not the only characters someone should be concerned about, which is why in a previous thread I highly encouraged htmlentities over htmlspecialchars, so that if you do need to utilize a lot of the UTF-8 characters, you have the ability to do so.

**felgall** · Oct 15, 2012 15:19

Originally Posted by cpradio

< and & are not the only characters someone should be concerned about, which is why in a previous thread I highly encouraged htmlentities over htmlspecialchars, so that if you do need to utilize a lot of the UTF-8 characters, you have the ability to do so.

It all comes down to what characters are valid within the particular field in the first place. Only where there are characters that can validly appear in the field that might potentially be misinterpreted when included in HTML is it necessary to consider using an escaping function to convert those characters to entity codes where they appear.

For most fields in most situations the values that are valid will not allow any characters that could cause problems and so no escaping would be necessary.

It is primarily because so many people forget to properly VALIDATE their data in the first place that so many people associate escaping functions with security.

**cpradio** · Oct 15, 2012 15:29

Originally Posted by felgall

It is primarily because so many people forget to properly VALIDATE their data in the first place that so many people associate escaping functions with security.

I can agree with that, and in the cases you need to have HTML formatting, I typically lean towards strip_tags to remove the tags that I don't want to allow.

However, I do use htmlentities quite a bit when dealing with content in other countries as umlauts(sp?) are much more frequent there and by using it, I can guarantee that if the browsing user overrides my encoding, it will likely still show up properly.

**vinpkl** · Oct 16, 2012 04:14

hi

This query works fine

PHP Code:


qry="select sum(cost) from table

But below query is giving error call to undefined function sum()

PHP Code:


qry="select". mysql_real_escape_string(sum(cost))." from table";

vineet

**cpradio** · Oct 16, 2012 05:10

And it should, because you do not need mysql_real_escape_string there, and sum is not a function in PHP, it is a MySQL function.

So your first query is just fine.

99% of the time you ONLY need to use mysql_real_escape_string for your WHERE clauses when they are comparing database data to user entered data.
There are rare 1% opportunities where you MAY need to use it in other locations, but I wouldn't worry about that, I don't think you will run into that 1% for a while and by that time, you'll understand when you need to do it and when you don't.

Keep in mind, mysql_real_escape_string is used to sanitize user entered data, not data already stored in your database.

**vinpkl** · Oct 16, 2012 06:48

hi cp

If in the product table all data has been entered by myself

and nothing has been entered by my customers(users registered with my site)

then i will write my query for displaying products on product page

without mysql_real_escape_string

PHP Code:


$qry = "select * from product_table where product_id=50"

I m correct ?

vineet

**cpradio** · Oct 16, 2012 06:53

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

PHP Code:


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

Granted, a even more secure query would be

PHP Code:


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

**vinpkl** · Oct 16, 2012 07:08

hi cp

product_id is added in the database automatically while adding product.

But when user clicks any <a> href link containing product id, then it becomes user initiated.

Is it so ?

Secondly

if i want to show only those products whose status='Yes', then mysql_real_escape_string is required or not.

This status is set by me in the database.

PHP Code:


<?

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

and Status ='". mysql_real_escape_string('YES')"'";  

?>

vineet

**cpradio** · Oct 16, 2012 07:13

Originally Posted by vinpkl

hi cp

product_id is added in the database automatically while adding product.

But when user clicks any <a> href link containing product id, then it becomes user initiated.

Is it so ?

Yes, because the product_id would be in the URL querystring and as a user, I could change it from product.php?product_id=50 to product.php?product_id='HA HA, LOOK WHAT I CAN DO'

Originally Posted by vinpkl

Secondly

if i want to show only those products whose status='Yes', then mysql_real_escape_string is required or not.

This status is set by me in the database.

If you have status as part of the query string (the product URL), then Yes, you need to use mysql_real_escape_string, however, if you are just going to hard code it, then no.

Example: product.php?product_id=50&status=Yes

PHP Code:


<?php

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

and Status ='". mysql_real_escape_string($_REQUEST['status'])"'";   

?>

Example 2: product.php?product_id=50

PHP Code:


<?php

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

and Status ='YES'";   

?>

**vinpkl** · Oct 16, 2012 08:00

hi cp

i created a new page "injection.php"

it contains a query without mysql_real_escape_string

PHP Code:


<?

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

PHP Code:


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

but that phone_covers table is not getting dropped/deleted.

vineet

**guido2004** · Oct 16, 2012 08:51

Originally Posted by 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

PHP Code:


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

Granted, a even more secure query would be

PHP Code:


$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:

Code:

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

**cpradio** · Oct 16, 2012 09:00

Originally Posted by vinpkl

hi cp

i created a new page "injection.php"

it contains a query without mysql_real_escape_string

PHP Code:


<?

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

PHP Code:


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

Originally Posted by 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:

Code:

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

Good catch, I did miss that.

**vinpkl** · Oct 16, 2012 09:22

Originally Posted by 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:

Code:

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

PHP Code:


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

**guido2004** · Oct 16, 2012 09:55

Originally Posted by 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

PHP Code:


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** · Oct 16, 2012 10:27

Originally Posted by 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

PHP Code:


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

Is it something to do with mysql or php version

vineet

User Tag List

Thread: mysql_real_escape_string and htmlspecialchars while matching

Thread Tools

Display

mysql_real_escape_string and htmlspecialchars while matching

Bookmarks

Bookmarks

Posting Permissions