mysql_real_escape_string and htmlspecialchars while matching

PHP
1

hi all

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


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

if($user_id = $row['user_id']
{
	/* do something */
}
?>

or


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

if($user_id == mysql_real_escape_string($row['user_id'])
{
	/* do something */
}
?>

or


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

if($user_id == htmlspecialchars($row['user_id'])
{
	/* do something */
}
?>

vineet

2

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

Example:

$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);
}
3

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…).

4

ok thanks for the replies

Do we use htmlspecialchars while outputting values in emails

vineet

5

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

6

hi cp

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


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



$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

7

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.

8

hi cp

This code works fine


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

But This code gives error


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

9

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:

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

10

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.

11

< 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.

12

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.

13

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.

14

hi

This query works fine


qry="select sum(cost) from table

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


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

vineet

15

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.

16

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


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

I m correct ?

vineet

17

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;
18

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.


<?
$qry = "select * from product_table where product_id=" . mysql_real_escape_string($_REQUEST['product_id'])."
and Status ='". mysql_real_escape_string('YES')"'";
?>

vineet

19

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’

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
$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
$qry = "select * from product_table where product_id=" . mysql_real_escape_string($_REQUEST['product_id'])."  
and Status ='YES'";   
?>
20

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