Need help with sql injections

[87dave87]

Hi,

 

I have just written some basic PHP code and want to know if the mysql_real_escape_string will suffice against sql injection attacks... I have been told to use PDO as real escape string is deprecated however I have absolutely no idea how to code that, so if someone would be kind enough to help me out with letting me know the below is safe or rewriting as PDO it would be much appreciated.

 

Heres my code for the insert page: -

 

<?php
$username="username";
$password="password";
$database="database";

$title=mysql_real_escape_string($_POST['title']);
$first_name=mysql_real_escape_string($_POST['first_name']);
$last_name=mysql_real_escape_string($_POST['last_name']);
$email_address=mysql_real_escape_string($_POST['email_address']);

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO collected VALUES ('','$title','$first_name','$last_name','$email_address')";
mysql_query($query);

mysql_close();
?>

 

 

[AyKay47]

the mysql extension has been deprecated which means in a later version of PHP it will be removed. Using MYSQLI or better yet PDO is encouraged.

Here is a link to the PDO extension to get you started.

 

To directly answer your question, mysql_real_escape_string()'s purpose is to make data safe to pass through an SQL statement. However it is not always needed depending on what type of data you are passing (e.g ints, floats). Sometimes casting a data type is sufficient.

[Christian F.]

It is not just unnecessary to escape numerical data, but it also offers no protection against SQL injections.

// Assume the user enters this as the ID for something:
$testID = "5 or 1=1 LIMIT 1";

// Which is then to be used inn this query:
$query = "SELECT `access_level` FROM `user_access` WHERE id = ";

// When we run the input through real_escape_string, we get...
$testID = $db->real_escape_string ($testID);
var_dump ($testID);
// Echos out: string(11) "5 or 1=1 LIMIT 1";

// Meaning we get the following completed string:
$query = "SELECT `access_level` FROM `user_access` WHERE id = 5 or 1=1 LIMIT 1";

Assuming that the first ID in the list is admin-privileges... Well, I guess you can see where that's going. Had we cast it to an integer instead, it would only have given us "5" in return and discarded the rest.

 

Also, it's not escaping that's deprecated. It's the old mysql library, as AyKay pointed out. The benefit of using Prepared Statements, in this case, is that you generally don't have to know how to properly escape each variable: The DB engine does it for you.

Edited March 3, 2013 by Christian F.