Well hello, coders! Having a hard time in tackling the SQL injection? This read consists of easiest techniques that most of the PHP development companies are referring to.
What exactly is SQL Injection?
SQL injection is a technique in which attacker can execute malicious SQL statements which can control the web application’s database server. Basically, it is web application vulnerability by which attacker can inject SQL statements via the input box in the web application, so it affects the SQL server of the web application. So, by this SQL injection attack, an attacker can bypass the security of a web application authentication or even fake specific users. With the SQL Injection attacker can get unauthorized access to sensitive and confidential data such as customer personal information, trade secrets, intellectual property and other sensitive information.
The way SQL injection works
To attack the web application with SQL injection, the attacker tries to find the input box in the web application which has SQL operations such as Login form.
The following is a server-side code which is used to authenticate users to the web application.
## Get the post data
<?php
$txtUserName = $_POST[‘txtUserName’];
$txtPassword = $_POST[‘txtPassword’];
?>
## SQL query to get the data
<?php
$sql = “SELECT id from users WHERE username = ‘”.$txtUserName.”’ AND password = ‘”.$txtPassword.”’”;
?>
## Execute the query
<?php
mysqli_query($sql);
?>
The above code snippet is a simple example to authenticate user’s details on the login form, where a user has to enter their username and password to access the member area of a web application. With the above example, a developer has checked user’s entered details with database username and password column.
With the above code, the attacker can easily bypass the authentication as there is no security has added to check the data.
The simple example to inject SQLquery to check the password field of a database is 1=1 which may run SQLcommand like this,
$sql = “SELECT id from users WHERE username = ‘”.$txtUserName.”’ AND password = ‘”.$txtPassword.”’ OR 1=1’”;
After the execution of this new query, the result is returned to the web application to be processed, resulting in an authentication bypass.
With the help of this simple SQL injection, the application will most likely log the attacker with the first record of the table and mostly the first record is most likely of an administrative user.
Reasons to attempt SQL injection?
An attacker may inject SQL statements to,
- Get information stored in the database.
- Remove or change the information stored in the database.
- Cause denial of service (DoS) by injecting SQL which will take more time to run the SQLqueries.
Impact of SQL injection
- As we have seen the above simple SQLinjection, an attacker will get administrative access to the web application so with the help of admin access attacker will try to access all other data.
- An attacker can use SQL Injection to bypass authentication or even impersonate specific users.
- With the SQL, we can select the data from the database with the entered user details, so SQL injection vulnerability will allow the attacker to get complete details of the data available at the database server
- With the help of SQLinjection attacker may change the data on database server
- An attacker can delete the data situated on the database server using SQL injection.
Ways to avoid SQL injection
- The most important way to protect from SQL injection is to use parameterized queries and prepared statements.These are the SQL statements which are sent to and parsed by the database server separately from any other parameters, so it is impossible for an attacker to inject malicious SQL. We can use PDO statements or mysqli statements to avoid SQLinjection.
For e.g.
- With the help of PDO
stmt = $pdo->prepare(‘SELECT id FROM users WHERE username = :txtUserName
);
$stmt ->bindParam(‘:username, $txtUserName);
$stmt ->bindParam(‘:password, $txtPassword);
$stmt ->execute();
- With the help of MySQLi
stmt = $dbConnection->prepare(‘SELECT * FROM users WHERE username = ?’);
$stmt->bind_param(‘username’, $ txtUserName);
$stmt->bind_param(password, $txtPassword);
$stmt->execute();
$resData = $stmt->get_result();
while ($rowUser = $resData->fetch_assoc()) {
// perform operation
}
- Use built-in PHP functions to avoid SQL injections.
- mysqli_real_escape_string()
The mysqli_real_escape_string function is used to escape special characters in a string.
- trim()
The trim() is used to remove the whitespaces and other predefined characters from the both sides (left and right) from the string.
- stripslashes()
The Stripslashes() function returns a string with the backslashes (\) removed which were added by the PHP addslashes() function.
Summary
To conclude, SQL injection is one of the most common security problems in the web applications today.To avoid SQL injections most useful way is to use prepared statements and parameterized queries.To prevent from SQL injection we can also do constrain and sanitize the input data entered by the user. And also perform the proper validations for user’s input by validating for type, length,and range. I hope this post resolves all your queries, and if it doesn’t, you may feel free to drop your questions in the comments section and I’ll be glad to respond.
