What is SQL Injection and How to Prevent it in PHP

Do you think your SQL database is secure from instant destruction? Yes, instant destruction because I am talking about SQL injection. It is a combination of two words that is: SQL + injection. Here, the injection doesn’t mean the medical terminology. You always have to take care of the SQL injection In PHP while writing any SQL query. The attackers may inject some additional string or character that may break down your entire flow. So, this is very terrifying if someone will break your code. In this post, I am going to explain to you about SQL Injection attack with examples. If you won’t prevent the SQL injection vulnerability then you may lose the data. Even data can be altered or dropped out from the database. Also, I will show you the way of SQL injection prevention. So, stay head up with this post, and let’s dive into it.

What is SQL Injection?

Before taking any example, firstly, let’s know about it. This is a code injection technique used to attack a data-driven application. In this type of attack, the malicious SQL statements are inserted into the database table. This will alter your SQL query and will perform some unwanted operations that you cannot imagine. The SQL injection is mostly known as an attack vector for websites but can be used to attack any type of SQL database.

In this type of attack, the attacker can guess the table name or a kind of parameters of the SQL query. This is a very old technique in the terms of Web Development.

How to Install LAMP Stack in Ubuntu 20.04 LTS

How SQL Injection Works?

Here, I will show you a working example of the SQL attack that attackers may do. Mostly, you create an e-commerce store locally as a project. Let’s say you have a contact form there. That is just for storing the user’s comment or message into the database table.

Here, I have created a form with some inputs for name, email and message.

<form action="save-contact.php" method="POST">

    <div class="form-group">
        <label for="name">Name </label>
        <input type="text" name="name" id="name" placeholder="Your name"/>
    </div>

    <div class="form-group">
        <label for="email">Email </label>
        <input type="email" name="email" id="email" placeholder="Your email"/>
    </div>

    <div class="form-group">
        <label for="message">Message </label>
        <textarea name="message" id="message" placeholder="Your message"></textarea>
    </div>

    <button type="submit" class="btn btn-success"> Send </button>

</form>

The form has action and it will send the form data to that action. So, let’s get the inputs on the action page. Your code will look something like this. You will be extracting the form data and will assign it to a variable respectively.

<?php

$name = $_POST['name'];
$email = $_POST['email'];
$message = $_POST['message'];

// check if email already exists
mysqli_query($conn, "SELECT * FROM contacts WHERE email = $email");


// your other code

Now, you are trying to check if that user’s email already exists. Your query is SELECT * FROM contacts WHERE email = $email. Is it the correct way that you are thinking?

No ! it is totaly insecure way.

In fact, you are inviting the attackers to do SQL injection in PHP on your database through the above query. Here, the attackers may guess the tables easily. Everyone knows that there would be an order table in the database in an e-commerce store. So what if the attackers will try to hamper with the above code.

SELECT * FROM contacts where email = 'something@gmail.com'; truncate orders;

Now, take a look at the above query. The SQL statement will try to fetch the contact of the given email. After that due to the end of the statement (;) it will execute the next statement. Here, the attackers have injected another query with the email parameters.

So, it would clean out the entire rows of orders table. Isn’t it terrific? Now, you have a clear picture of the SQL injection vulnerability that how may attackers hamper your database?

Now, it’s time to know the way to prevent the attack of SQL injection. Slightly, we will see the step by step here.

Check Email Available in PHP MySQL Using jQuery and Ajax

SQL Injection Prevention in PHP

You will always require to take care while writing the SQL queries in PHP. There are two ways to prevent the vulnerable attack of spoofing.

1. Sanitize inputs data
2. Use prepared statements

Now, the question is how you will sanitize the user’s input? So, let’s see.

Sanitize Inputs Data

PHP provides a predefined function called mysql_real_escape_string(). This function is pretty wise and Basically, what it does? It remove all special characters in a string so that they lose their meaning when used by the database.

Let’s say you have input as a string like You're a programmer. Here the string contains a single quote (‘). So attackers may use this type of string manipulation to hamper the database. But due to the mysql_real_escape_string() given string will be treated and produced as You\'re a programmer.

Here the single quote will be having a backslash. So, now the given string will be passed without any tempering the database.

But the thing is this function was applicable before PHP 7. It is improved in PHP 7 with mysqli. So, the function changed as-

mysqli_real_escape_string(connection, escapestring)

For the object oriented style you can use like this-

$mysqli -> real_escape_string(escapestring)

But, is that enough for SQL injection prevention in PHP?

The answer is No. So, let’s see the second way where we will use the prepared statements.

How to Implement jQuery Datatable in PHP with MySQL

Use Prepared Statements in PHP

By using the prepared statements, you can execute database queries safely. It is safer than a normal execution of the query. But, you might be wondering how this will prevent SQL injection? The answer is that it doesn’t send the written raw query to the database. Instead of that, it tells the database we are sending the structure of the query. In other words, it doesn’t execute the SQL query directly with the parameters. Rather than it sends a structure of the SQL query to the database. So, the database won’t have the exact query with that value that attackers injected through the query.

Firstly, it will prepare the SQL statement and once it is prepared, we will pass the value as a parameterized inputs. It will just create a placeholder for the inputs. But it will pass the parameters after the statement is prepared. So that the database can fill the gaps by putting the inputs to the query structure as we sent before. Take a look at the prepared statements.

<?php

$servername = "localhost";
$dbname = "databasename";
$username = "username";
$password = "password";

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

// Checking connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare and bind
$stmt = $conn->prepare("INSERT INTO contact (name, email, message) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $email, $message);

// set parameters and execute
$name = "John Doe";
$email = "john@example.com";
$message = "This is a message for the SQL Injection prevention"
$stmt->execute();

$name = "Stuart";
$email = "stuart@example.com";
$message = "This is a message for the preapred statement"
$stmt->execute();

echo "Contacts created successfully";

// close statement
$stmt->close();

// close connection
$conn->close();

?>

CRUD Application in PHP 8 Using Prepared Statement

Let me explain if you are new to the Prepared statement.

• After creating the database connection, I have prepared a SQL statement using the prepare() function using the connection object. Actually, this is an object-oriented way, you can use this in a procedural way too.
• In the prepare() function, I have passed the SQL query with placeholders of expected inputs with a question mark (?) symbol. Let’s say, I have three inputs for name, email, and message. Also, I am expecting to have the string value for every input.
• Now, in the next line, I have bound the parameters to that prepared statement. Here, s denotes the string as a data type of input.
• These are the types that can be passed through the argument here –
  • i – integer
  • d – double
  • s – string
  • b – BLOB

• In the next line, I have set the parameters to the specified params.
• After assigning the values to the params, lastly, executed the statement.

That’s it for the prepared statements.

There are more ways to prevent SQL injection attack. If you have deployed the PHP application to the server then you can enable the Firewall, CSRF (Cross Site Request Forgery), Cross Scripting, etc. So, that you can disable the un-authorized access of your application URL and data as well.

Drag and Drop Multiple File Upload in PHP Using Dropzone js

Now, a days, if you are going with the frameworks then you no need to worry about the SQL injection. They can handle the security issue but you have to sanitize the inputs.

If you are creating the RESTful APIs then you can use access token for the HTTP requests. That is also a secure way to prevent the un-authorized access of your API.

Conclusion

I have given the idea of SQL injection and how the attacker may hamper your database. If you won’t take care of the inputs then SQL injection vulnerability can make instant destruction of the database. I have shown you the SQL injection prevention steps. You can follow these steps to make a secure and authentic application in PHP. Make a good practice to validate and sanitize data properly which are coming through the form or any request. I hope you will take care of the security concern while creating any application. Thank you.