How can I Prevent SQL Injection in PHP?

In this article, we will use the best way to do modification into SQL query to prevent SQL injection using PHP.

We will use parameterized queries and prepared statements to protect a database from SQL injection by hackers.

What is SQL injection?

SQL injection in PHP is a common and popular technique or method used by hackers to access the database of your website and can destroy it OR they can also use our information in the wrong way.

When malicious code embedded into SQL query by user input inserted then our website or application becomes vulnerable and the hacker can access our whole database.

So let’s start to become a father of hackers 😉

Use SQL Parameters and Prepared Statements for Protection

To protect a website OR application from SQL injection by attackers, you can use SQL Parameters and Prepared statements.

SQL Parameters are values that are added in SQL query and Prepared statements are an execution method of SQL query.

You have two options to do this.

Using MySqli:

See this code below, how you can use a prepared statement and parameters.

<?php
	$servername = "localhost";
	$username = "username";
	$password = "password";

	// Create connection
	$connection = new mysqli($servername, $username, $password);

	// Check connection
	if ($connection->connect_error) {
		die("Connection failed: " . $connection->connect_error);
	} else {
		echo "Connected successfully";
	}

	$stmt = $connection->prepare('SELECT * FROM students WHERE name = ?');
	$stmt->bind_param('s', $name); 
	// 's' specifies the variable type => 'string'

	$stmt->execute();

	$result = $stmt->get_result();
	while ($row = $result->fetch_assoc()) {
	    echo "student_id: " . $row["student_id"]. " - Name: " . $row["name"]. "<br>";
	} else {
	    echo "0 results";
	}

	$connection->close();
?>

Using PDO:

See this code below, how you can use a prepared statement and parameters.

<?php
	$stmt = $pdo->prepare('SELECT * FROM students WHERE name = :name');

	$stmt->execute(array('name' => $name));

	foreach ($stmt as $row) {
	    echo "student_id: " . $row["student_id"]. " - Name: " . $row["name"]. "<br>";
	}
?>

NOTE: I want to let you know that, when we are using PDO to connect with MySQL database then prepared statement are not allowed by default. You have to enable this by disabling emulation of prepared statements.

Look at the example connection creating with PDO:

<?php

	$pdo = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'password');

	$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
	$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

?>

Using mysql_real_escape_string():

We can also use mysql_real_escape_string() function to escape characters from string to prevent SQL injection. But if you are using a recent version of PHP (7.*.*) then this function will no longer be available in an entirely new version of PHP.

So for this you can use escape_string(). We can also still use mysql_real_escape_string() function but its only for legacy of PHP.

mysql_real_escape_string() function take a string by prevent SQL injection in PHP and return us to same string and escaped extra quotes(”) from string and return safe SQL query.

Look at the example:

<?php

//DB Connect

	$unsafe_variable = $_POST["student_name"];
	$safe_variable = mysql_real_escape_string($unsafe_variable);

	mysql_query("INSERT INTO students (name) VALUES ('" . $safe_variable . "')");

//DB Disconnect

?>