Trying to understand where to use mysql_real_escape_string

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
djs1971
Forum Newbie
Posts: 14
Joined: Tue Jun 01, 2010 4:33 pm

Trying to understand where to use mysql_real_escape_string

Post by djs1971 »

Newbie, trying to get to grips with the use of mysql_real_escape_string which I believe I need to do to escape various characters as well as help protect against sql injection.

As a starting point, I'm looking at this section of my code which I use to update my database when a user changes a score, effort grade or note in a form - could anyone point me in the right direction of where exactly to insert the mysql_real_escape_string. I'm sure as I learn more the online examples I've seen will make sense, but at the moment I'm struggling with this one! Thanks in advance for any suggestions.

Code: Select all

$result = mysql_query("SELECT admin FROM students WHERE year='$_GET[year]'");
	
	
	while($row = mysql_fetch_array($result))
	  {
		
		
		$Aut7str = 'Aut7';
		$Aut7_Effortstr = 'Aut7_Effort';
		$Aut7_Notestr = 'Aut7_Note';
		$Aut7admin = $row[admin];
		$Aut7_Effortadmin = $row[admin];
		$Aut7_Noteadmin = $row[admin];
		$Aut7together = $Aut7str.$Aut7admin;
		$Aut7_Efforttogether = $Aut7_Effortstr.$Aut7_Effortadmin;
		$Aut7_Notetogether = $Aut7_Notestr.$Aut7_Noteadmin;
		
		
				
		$sql="UPDATE assess
		SET Aut7='$_GET[$Aut7together]', Aut7_Effort='$_GET[$Aut7_Efforttogether]', Aut7_Note='$_GET[$Aut7_Notetogether]'
		WHERE admin='$row[admin]' AND subject_id='$_GET[subject]' ";
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Trying to understand where to use mysql_real_escape_stri

Post by mecha_godzilla »

mysql_real_escape_string is very useful but you might want to look at other functions such as strip_tags() and htmlentities as well. When I parse form values in my scripts I tend to use the following code:

Code: Select all

// Usage:
// parse_form('name',40);

function parse_form($value,$length) {

if (!get_magic_quotes_gpc()) {
    $value = addslashes(strip_tags(trim($_POST[$value])));
    $value = @substr($value, 0, $length);
} else {
    $value = strip_tags(trim($_POST[$value]));
    $value = @substr($value, 0, $length);
}

}
This allows me to escape the values and truncate them to a specific length (whatever the allowed character length of the column in the record is set to).

However, maybe we're getting ahead of ourselves here! :D The idea with mysql_real_escape_string() is that it will return an 'escaped' version of whatever data you send it, but when the data is returned it has to be assigned somewhere, so the usage is:

Code: Select all

$my_escaped_value = mysql_real_escape_string($my_potentially_unsafe_value);
Whatever data you receive is then ready to go into your query. The main (but not only) purpose of escaping a string is to stop the format of the DB query being broken. As an example if you have

Code: Select all

$query = "SELECT record_id FROM sample_table WHERE id = $my_potentially_unsafe_value";
and $my_potentially_unsafe_value contains something that looks like this

Code: Select all

\'' OR 1'
then PHP would interpret this as

Code: Select all

$query = "SELECT record_id FROM sample_table WHERE id = \'' OR 1'";
which might break the query when MySQL processes it. To show how this works, try the following code; remember that mysql_real_escape_string() only works if you've specified a DB connection beforehand:

Code: Select all

$my_potentially_unsafe_value = "\'' OR 1'";
$my_escaped_value = mysql_real_escape_string($my_potentially_unsafe_value);
	
$unsafe_query = "SELECT record_id FROM sample_table WHERE id = $my_potentially_unsafe_value";
print_r($unsafe_query);
echo '<br />';
// SELECT record_id FROM sample_table WHERE id = \'' OR 1'
$safe_query = "SELECT record_id FROM sample_table WHERE id = $my_escaped_value";
print_r($safe_query);
// SELECT record_id FROM sample_table WHERE id = \\\'\' OR 1\' 
There are two final issues to consider:

1. If magic quotes are on in your server settings, it will automatically be escaping ' (single quotes) which can cause mysql_real_esape_string to escape your values twice.

2. Malformed queries also make SQL injection attacks possible; the following example (posted by strata_ranger on the PHP manual page for mysql_real_esape_string) shows how this works:

Code: Select all

// E.g. attacker's values
$_POST['username'] = '';
$_POST['password'] = "' OR user = 'administrator' AND '' = '";

// Malformed query
$query = "SELECT * FROM users WHERE user='$_POST[username]' AND password='$_POST[password]'";

echo $query;

// The query sent to MySQL would read:
// SELECT * FROM users WHERE user='' AND password='' OR user='administrator' AND ''='';
// which would allow anyone to gain access to the account named 'administrator'
If you need any other help please say so.

HTH,

Mecha Godzilla
djs1971
Forum Newbie
Posts: 14
Joined: Tue Jun 01, 2010 4:33 pm

Re: Trying to understand where to use mysql_real_escape_stri

Post by djs1971 »

Wow, thanks for such a detailed reply. i will spend some time reading through this see how I go implementing it. Thanks again for your time and effort :D
djs1971
Forum Newbie
Posts: 14
Joined: Tue Jun 01, 2010 4:33 pm

Re: Trying to understand where to use mysql_real_escape_stri

Post by djs1971 »

Hi again! Ok, had a good read through this and am starting to get to grips however, first efforts at implementing are not working.

I've concentrated on just one of the 3 pieces of data being updated by my code to test out my changes on.

This is what I've tried but still will not let me update any text with and ' in it eg: George's however if I enter George or George\'s it will update the db fine.

Code: Select all

$result = mysql_query("SELECT admin FROM students WHERE year='$_GET[year]'");
	
	
	while($row = mysql_fetch_array($result))
	  {
		
		
		$Aut7str = 'Aut7';
		$Aut7_Effortstr = 'Aut7_Effort';
		$Aut7_Notestr = 'Aut7_Note';
		$Aut7admin = $row[admin];
		$Aut7_Effortadmin = $row[admin];
		$Aut7_Noteadmin = $row[admin];
		$Aut7together = $Aut7str.$Aut7admin;
		$Aut7_Efforttogether = $Aut7_Effortstr.$Aut7_Effortadmin;
		$Aut7_Notetogether = $Aut7_Notestr.$Aut7_Noteadmin;
		
                // added this to try and escape $Aut_Notetogether
		$escapedAut7_Notetogether=mysql_real_escape_string($Aut7_Notetogether);
			
		$sql="UPDATE assess
		SET Aut7='$_GET[$Aut7together]', Aut7_Effort='$_GET[$Aut7_Efforttogether]', Aut7_Note='$_GET[$escapedAut7_Notetogether]'
		WHERE admin='$row[admin]' AND subject_id='$_GET[subject]' ";
		
djs1971
Forum Newbie
Posts: 14
Joined: Tue Jun 01, 2010 4:33 pm

Re: Trying to understand where to use mysql_real_escape_stri

Post by djs1971 »

Ah!!! Success - some trial and error, and then google searching based on ideas from your helpful notes in your reply led me to this...

Code: Select all

$result = mysql_query("SELECT admin FROM students WHERE year='$_GET[year]'");
	
	
	while($row = mysql_fetch_array($result))
	  {
		
		
		$Aut7str = 'Aut7';
		$Aut7_Effortstr = 'Aut7_Effort';
		$Aut7_Notestr = 'Aut7_Note';
		$Aut7admin = $row[admin];
		$Aut7_Effortadmin = $row[admin];
		$Aut7_Noteadmin = $row[admin];
		$Aut7together = $Aut7str.$Aut7admin;
		$Aut7_Efforttogether = $Aut7_Effortstr.$Aut7_Effortadmin;
		$Aut7_Notetogether = $Aut7_Notestr.$Aut7_Noteadmin;
		
		
		
		$sql="UPDATE assess
		SET Aut7='".mysql_real_escape_string($_GET[$Aut7together])."', Aut7_Effort='".mysql_real_escape_string($_GET[$Aut7_Efforttogether])."', Aut7_Note='".mysql_real_escape_string($_GET[$Aut7_Notetogether])."'
		WHERE admin='$row[admin]' AND subject_id='$_GET[subject]' ";
		
		
Works brilliantly.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Trying to understand where to use mysql_real_escape_stri

Post by Jonah Bron »

djs1971 wrote:

Code: Select all

$result = mysql_query("SELECT admin FROM students WHERE year='$_GET[year]'");
Ttgaaaaaa!
You need to scan $_GET['year'] with mysql_real_escape_string().

Code: Select all

$year = mysql_real_escape_string($_GET['year']);
$result = mysql_query('SELECT admin FROM students WHERE year = "' . $result . '"');
I would say use intval() instead, but there might be leading zeros, I don't know what year format you're using.
djs1971
Forum Newbie
Posts: 14
Joined: Tue Jun 01, 2010 4:33 pm

Re: Trying to understand where to use mysql_real_escape_stri

Post by djs1971 »

Brilliant, thanks for the heads up Jonah - this is all completely new to me so any help appreciated!!!

I've adjusted the code as you explained - now reads as:

Code: Select all

$year = mysql_real_escape_string($_GET['year']);
	$result = mysql_query('SELECT admin FROM students WHERE year = "' . $year . '"');
	
	
	while($row = mysql_fetch_array($result))
	  {
		
		
		$Aut7str = 'Aut7';
		$Aut7_Effortstr = 'Aut7_Effort';
		$Aut7_Notestr = 'Aut7_Note';
		$Aut7admin = $row[admin];
		$Aut7_Effortadmin = $row[admin];
		$Aut7_Noteadmin = $row[admin];
		$Aut7together = $Aut7str.$Aut7admin;
		$Aut7_Efforttogether = $Aut7_Effortstr.$Aut7_Effortadmin;
		$Aut7_Notetogether = $Aut7_Notestr.$Aut7_Noteadmin;
		
		
		
		$sql="UPDATE assess
		SET Aut7='".mysql_real_escape_string($_GET[$Aut7together])."', Aut7_Effort='$_GET[$Aut7_Efforttogether]', Aut7_Note='".mysql_real_escape_string($_GET[$Aut7_Notetogether])."'
		WHERE admin='$row[admin]' AND subject_id='$_GET[subject]' ";
Do I need to do anything similar in the final 'WHERE' section of the code within the UPDATE section?
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Trying to understand where to use mysql_real_escape_stri

Post by mecha_godzilla »

Yes - using $_GET[subject] in your query is dangerous :mrgreen:

To avoid making your DB queries unreadable, you should escape all the values with mysql_real_escape_string() before they're used in any queries. Also, when you use " (double quotes) as opposed to ' (single quotes) PHP will interpret any variables automatically.

It's probably better not to mix-and-match values derived from variables, arrays and $_GET requests, so just convert everything to variables first; rewriting your code it would look as follows:

Code: Select all

// Capture and escape $_GET values
$year = mysql_real_escape_string($_GET['year']);
$subject = mysql_real_escape_string($_GET['subject']);

// Set-up and run query
$conn = mysql_connect('your','server','details');
mysql_select_db('name_of_database',$conn);
$query = "SELECT admin FROM students WHERE year = '$year'";
$result = mysql_query($query, $conn) or die(mysql_error());
$number_of_results = mysql_num_rows($result);

// Parse results
if ($number_of_results != 0) {

    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    
        $Aut7str = 'Aut7';
        $Aut7_Effortstr = 'Aut7_Effort';
        $Aut7_Notestr = 'Aut7_Note';
        $Aut7admin = mysql_real_escape_string($row[admin]);
        
        $Aut7together = $Aut7str . $Aut7admin;
        $Aut7_Efforttogether = $Aut7_Effortstr . $Aut7admin;
        $Aut7_Notetogether = $Aut7_Notestr . $Aut7admin;
        
        $sql = "UPDATE assess
                SET Aut7='$Aut7together', Aut7_Effort='$Aut7_Efforttogether', Aut7_Note='$Aut7_Notetogether'
                WHERE admin='$Aut7admin' AND subject_id='$subject'";
        
        $update_result = mysql_query($query, $conn) or die(mysql_error());
    
    }

} else {

    // Do nothing or show error message because no results were found

}
Please note that this is just my idea of coding 'style' but I find it makes my queries very readable - I always know where the values have been derived from and whether they've been escaped or not. Also, in your script you're capturing the same $row['admin'] value three times when you can just reuse the first instance. I can't guarantee that query will definitely work for you but it might be of some use :mrgreen:

HTH,

M_G
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Trying to understand where to use mysql_real_escape_stri

Post by VladSun »

mecha_godzilla wrote:mysql_real_escape_string is very useful but you might want to look at other functions such as strip_tags() and htmlentities as well. When I parse form values in my scripts I tend to use the following code:

Code: Select all

// Usage:
// parse_form('name',40);

function parse_form($value,$length) {

if (!get_magic_quotes_gpc()) {
    $value = addslashes(strip_tags(trim($_POST[$value])));
    $value = @substr($value, 0, $length);
} else {
    $value = strip_tags(trim($_POST[$value]));
    $value = @substr($value, 0, $length);
}

}
I wouldn't use this. One should always use SQL engine provided functions to escape DB input.
One should escape output according to the view target context (i.e. strip_tags() should be performed only if the target view is HTML and you do not want to permit user HTML code).
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Trying to understand where to use mysql_real_escape_stri

Post by Mordred »

I concur with VladSun (Heya!)

Here's a detailed overview on when to use mres and other methods: The Unexpected SQL injection
Post Reply