Sanitize User Input with MySQL

Never believe your user would always provide you with the correct or expected input. People can play around with your security and mess up with the important data. You might end up losing your customers or their data (emails or passwords) or may be your website.

Always protect your code and database from hacks and intrusions by sanitizing the user inputs.

How to sanitize user input?

1) Put a size limit on every input field.

substr($_POST['name'], 0, 50);

2) Check whether input’s context is per expected or not, i.e., where you are expecting an integer do not entertain alphabets or others.

is_numeric($_POST['name'])

3) Type cast inputs wherever necessary.

$id = (int)$user_id;
// or use sprintf
$query = sprintf("SELECT * FROM users WHERE id='%d'", mysql_real_escape_string($id) );

4) Process them through the PHP’s function mysql_real_escape_string(). This function escapes character which have special meaning to SQL engines.

$address = mysql_real_escape_string($address, $dblink);
//do it on whole user data in one go
 $_POST = mysql_real_escape_array($_POST);
//or for more control
 foreach ($_POST as $key => $value) {
 $_POST[$key] = mysql_real_escape_string($value);
 } 

//If magic_quotes_gpc is enabled, apply stripslashes() first before
//applying mysql_real_escape_string to the data.
//Else,this function will escape the data twice.

5) Never pass user inputs directly to the SQLs.

//Never do this
$sql = "Select * from tablename where $condition-from-user"

6) Try to hard-code SQLs as much as possible.

7) Do not fetch * (Select *) wherever you know what you need to fetch (Select name, id).

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>