Prevent MYSQL Injection in PHP (Simple Way)

PHP / MYSQL Tutorials

Prevent MYSQL Injection in PHP (Simple Way)

This will teach that how to prevent mysql injection in php and help you secure your scripts and MySQL statements.

What is SQL Injection?

SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

SQL Injection Example

$name = $_POST['username'];
$query = "SELECT * FROM `tbl_name` WHERE `name` = '$name' ";

As you can see the value the user enters into the URL variable username will get assigned to the variable $name and then placed directly into the SQL statement. This means that is possible for the user to edit the SQL statement.

$name = "admin' OR 1=1 -- ";
$query = "SELECT * FROM `tbl_name` WHERE `name` = '$name' ";

The SQL database will then receive the SQL statement as the following:

SELECT * FROM `tbl_name` WHERE `name` = 'admin' OR 1=1 --'

Which is valid SQL, and instead of returning data for the user, the statement would return all data in the table `table_name`. This is not something anyone wants in their web applications. This Tutorial will show you how to prevent this type of vulnerability.

So, How to prevent MYSQL Injection in php a simple way?

This problem has been known for a while and PHP has a specially-made function to prevent these attacks. All you need to do is use a function mysql_real_escape_string().

What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(‘) a user might enter with a MySQL-safe substitute, an escaped quote \’.

Lets try out this function on our two previous injection attacks and see how it works.

$name = mysql_real_escape_string($_POST['username']);
$query = "SELECT * FROM `tbl_name` WHERE `name` = '$name' ";

Let’s create a proper function for it and you can call it by any name, here i am going to name it as ‘mres’.

function mres($var){
	if (get_magic_quotes_gpc()){
		$var = stripslashes(trim($var));
	}
	return mysql_real_escape_string(trim($var));
}

You can now simply use this function like this.

$name = mres($_POST['username']);
$query = "SELECT * FROM `tbl_name` WHERE `name` = '$name' ";

OTallu