PHP - Learning MySQL - page 460

$input=mysql_real_escape_string($_POST["username"])
Consider how each of the sample input strings is processed. Without the escaping step,
the input:
' OR '' = '
for both username and password produces the SQL query:
SELECT * FROM users WHERE username='' OR '' = '' AND password='' OR '' = ''
but after the escaping step, we get the query:
SELECT * FROM users WHERE username='\' OR \'\' = \'' AND password='\' OR \'\' = \''
Similarly, the input string:
testusername'; DELETE FROM users;
results in the SQL query:
SELECT * FROM users WHERE username='testusername'; DELETE FROM users;'
AND password='testusername'; DELETE FROM users;'
without escaping, but:
SELECT * FROM users WHERE username='testusername\'; DELETE FROM users;'
AND password='testusername\'; DELETE FROM users;'
after it.
Note that the escaping step also helps avoid problems with input strings that legiti-
mately have an apostrophe in them; for example, if we have an SQL query to select
users by surname, the surname “D'Arcy” would result in an invalid query:
SELECT * FROM users WHERE surname='D'Arcy'
Escaping the backslash before the apostrophe solves the problem.
SELECT * FROM users WHERE surname='D\'Arcy'
PHP has a magic_quotes_gpc directive that, if set in the php.ini configuration file, au-
tomatically escapes single quotes and double quotes in data sent from the client's
browser from web forms or cookies. However, this in turn causes other problems and
is disabled in the upcoming PHP version 6.
We can write a function to limit the length of the input data, and escape semicolons
and, if needed, single and double quotes. This function—let's call it clean( ) —takes
two arguments—the input data to be cleaned, and the maximum length the data is
allowed to have:
// Secure the user data by escaping characters and shortening the
// input string
function clean($input, $maxlength)
{
// Access the MySQL connection from outside this function.
global $connection;