Page 1 of 1

Trying to understand where to use mysql_real_escape_string

Posted: Sat Nov 20, 2010 6:37 am
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]' ";

Re: Trying to understand where to use mysql_real_escape_stri

Posted: Sat Nov 20, 2010 7:05 pm
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

Re: Trying to understand where to use mysql_real_escape_stri

Posted: Sun Nov 21, 2010 5:46 am
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

Re: Trying to understand where to use mysql_real_escape_stri

Posted: Sun Nov 21, 2010 4:09 pm
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]' ";
		

Re: Trying to understand where to use mysql_real_escape_stri

Posted: Sun Nov 21, 2010 4:40 pm
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.

Re: Trying to understand where to use mysql_real_escape_stri

Posted: Sun Nov 21, 2010 5:00 pm
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.

Re: Trying to understand where to use mysql_real_escape_stri

Posted: Sun Nov 21, 2010 5:13 pm
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?

Re: Trying to understand where to use mysql_real_escape_stri

Posted: Mon Nov 22, 2010 5:59 pm
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

Re: Trying to understand where to use mysql_real_escape_stri

Posted: Tue Nov 23, 2010 3:52 am
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).

Re: Trying to understand where to use mysql_real_escape_stri

Posted: Sun Jan 09, 2011 11:29 am
by Mordred
I concur with VladSun (Heya!)

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