Protect website from SQL Injection in PHP

By Faruque Ahamed Mollick

From the name SQL Injection, many of you can guess that someone injects SQL into your PHP application. Well, obviously it is not good to let a user inject SQL code inside your web application.

But how it can be possible? How is it possible to inject SQL code into the application?

The simple login form looks like the given code below:



<?php
$host = "localhost";
$user = "root";
$password = "";
$db = "ico";
$conn = mysqli_connect($host, $user, $password, $db);

if (isset($_POST['submit'])) {
$username = $_POST[ 'username' ];
$password = $_POST[ 'password' ];
$query = "SELECT * FROM users WHERE username='$username' AND pw='$password'";
$runquery = mysqli_query($conn, $query);

if ( mysqli_num_rows( $runquery ) > 0 ) {
  echo "Successfully logged in";
}
else {echo "Log in failed!";}
}

?>

<form method="post" action="test2.php">
  <input type="text" name="username"><br/>
  <input type="password" name="password"><br/>
  <input type="submit" name="submit" value="Login">
</form>

 

The username and password come from user input and then these are taken to the variable which will use inside the MySQL query. below is the SQL query to find the user from the database table:

$query = "SELECT * FROM users WHERE username='$username' AND pw='$password'";

The SQL injection code also can be sent by a user from the login input fields.

Suppose the user enters ‘ or ‘1‘=’1 in the password input field. Can you guess what will be happened if a user does that?

Simple PHP code for file uploading process

Let’s see how the SQL query will be looks if a user enters ‘ or ‘1‘=’1 in the password field. The MySQL query will look like below;

$query = "SELECT * FROM users WHERE username='samim' AND pw='' or '1'='1'";

What does the above query mean? It tells MySQL to find all rows with a username that is “samim” and a password equal to an empty string OR “1=1”. To represent that a bit more logically:

( username = “samim” and password = “” ) || ( 1 = 1 )

Now, 1=1 is always going to be true, so this is equal to:

( false ) || ( true )

Which means the record from the MySQL table will retrieve and thus the user will be able to successfully login without knowing the password.

Disable direct access to the PHP include file

Difference between PHP include and require satements

So obviously you have to prevent this. This is called “SQL Injection” and it is a big security issue for a website. So now I am going to tell you how you can protect your web application from SQL injection.

How to protect websites from SQL Injection in PHP?

Luckily there is an easy way to protect a website from SQL injection. You can use the mysql_prepare function.

Using mysql_prepare function you are not going to add the variable or value directly to the MySQL code. Below you can see that I have used a question mark instead of the variables and we have passed the variable in bind_param function.

$query = "SELECT * FROM users WHERE username=? AND pw=?";
$runquery = mysqli_prepare($conn, $query);

$runquery->bind_param( 'ss', $username, $password);
$runquery->execute();

If you’ve never seen prepared statements before, this may look a little weird to you. But you will feel comfortable soon after some practice. Basically what’s happening is that you are creating a template for what the SQL statement will be, but it is not running the SQL query in real.

The bind_param() PHP function is where you attach variables to the dummy values in the prepared template. Notice how there are two letters in quotes before the variables. This tells the database the variable types. The s specifies a string value. For integer, you should use i.

Simple PHP Code To Check If URL Parameter Is Exist Or Not

How to get HTML form data in PHP variable?

You can notice that the question mark are not inside the quotation mark like I did with variables. Well, I didn’t forget. The reality is that there is no need of quotation mark. You are telling with that it will be a string when you call bind_param() function. So whenever any of the malicious SQL code will be provided by a user, it will still take that as a string and in this way, it will protect your website from SQL injection.

After you use $runquery->execute(), the query will be run.

The complete login form will look like below after that:

<?php
$host = "localhost";
$user = "root";
$password = "";
$db = "ico";
$conn = mysqli_connect($host, $user, $password, $db);

if (isset($_POST['submit'])) {
$username = $_POST[ 'username' ];
$password = $_POST[ 'password' ];
$query = "SELECT * FROM users WHERE username=? AND pw=?";
$runquery = mysqli_prepare($conn, $query);

$runquery->bind_param( 'ss', $username, $password);
$runquery->execute();

if ( mysqli_num_rows( $runquery ) > 0 ) {
  echo "Successfully logged in";
}
else {echo "Log in failed!";}
}
?>

<form method="post" action="test2.php">
  <input type="text" name="username"><br/>
  <input type="password" name="password"><br/>
  <input type="submit" name="submit" value="Login">
</form>

Now it will not possible to inject SQL. With this way, you can protect your website from SQL injection. The same process is also applicable if you are sending value from URL to database.

Leave a Reply

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