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!

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