code injection (DRAFT)

For people writing Web pages with forms and/or with SQL and PHP

SQL injection

Suppose you ask the user to type a numerical id into a form and the page that processes the form expects the result to be in a variable called id. You might have something like

$query="SELECT * FROM Users WHERE UserNum =$id;";

You'll check the result to see if the id exists. But if the user sets the id as

1 or 1=1

the resulting query will be

   SELECT * FROM Users WHERE UserNum =1 or 1=1;

which will select all the users, which might cause problems. Suppose instead that the user sets the id as

1; DROP TABLE Users;

The resulting query will be

   SELECT * FROM Users WHERE UserNum =1; DROP TABLE Users;

deleting the table completely. Here the malicious user needs to guess the name of the table, but if they use SHOW TABLES instead of DROP TABLE Users they can discover the table names anyway. If your query is more complicated you might think you'd be safe. E.g. a query of

$query="SELECT * FROM Users WHERE UserNum =$id AND year=3;";

would result in a query of

   SELECT * FROM Users WHERE UserNum =1; DROP TABLE Users; AND year=3;

which isn't valid, so none of the sql code will run. However, if the error message shows the faulty sql command in the error message, the malicious user can try again. By using trial and error they might get round the problem by setting the id as

1; DROP TABLE Users; #

thus commenting out the rest of the line.

Or they could try

id=0 UNION SELECT * FROM admin; #

bringing information in from another table.

Suppose the query was

$query="DELETE FROM Users WHERE UserNum =$id;";

Now an id of

1 or 1=1

will remove all the entries from the Users table.

Solutions

Be stricter about the type of value expected. In this case you could use

$id=intval($id);

You can try to remove semi-colons by doing

$id = ereg_replace( ";$", "", $id );
$id = ereg_replace( "\\\\", "", $id );

or more generally use

function mysql_fix_string($str) {

if(get_magic_quotes_gpc())
    $str=stripslashes($str);
return mysql_real_escape_string($str);
}

$u=mysql_fix_string(($_POST['user']));

SQL parameters

User input cannot be used as commands if SQL parameters are used -

$query='prepare statement from "INSERT INTO Users VALUES(?,?);"';
mysql_query($query);
$query='set @a="$txtNam",@b="$txtAdd";'
mysql_query($query);
$query='execute statement using @a,@b;';
mysql_query($query);
$query="deallocate prepare statement"; 
mysql_query($query);

HTML injection

Just as PHP code can be inserted, so can HTML or Javascript. If the id value from a form is echoed naively to the screen and the id variable has the value

<b>

then the rest of the page might be in bold. Harmless enough, but if instead it's

<code>alert("");</code>

then code might be run. HTML and PHP injection can be sanitized by using

function mysqlentities_fix_string($str) {
   return htmlentities(mysql_fix_string($str));

}

Testing pages

Add ?user=admin to the URL of a page. Look at the code of a page to choose better variable names than used here.
Variables (hidden ones too) that are passed from a form using GET can be set this way.
If the deprecated register_globals directive is set to on then $_GET['user'] would also exist as $user, which is dangerous. This feature has been DEPRECATED as of PHP 5.3.0 and REMOVED as of PHP 5.4.0.
Try inputting this - <code>alert("");</code>. If an alert box appears, beware.
Try inputting this - <?my_sql_query("show tables;")?>. If lots of extra output appears, beware.
Try inputting this - 1; SHOW TABLES; #. If lots of extra output appears, beware.

You can then try escaping and quoting parts of these statements.

Department of Engineering

IT Services