What is SQL injection?
This is when an attacker adds their own SQL statement to the original. Let’s take a login to a website as an example. A website may in this case take your username and password and compare these credentials to those stored in a database. In SQL this might be select count(user) from tableOfUsers where user = 'joe' and password = 'pass';. The statement looks okay. But if the password and username are not checked, someone could add themselves to the database, extract users and password or even delete the contents. In this case if a person was to add pass’; select 1; --. This would finish the SQL statement pass’;. The addition of select 1; would allow the attacker login access. -- at the end of the statement comments out anything else in the query. So the original query would have returned a one. The new query would have also returned one, thus allowing access.
How to stop this from happening
The simplest way to avoid these issues is to check the inputs before the query.
A simple method might be to disallow or strip out quote marks and dashes. This would stop that issue, but with passwords almost requiring these characters, this method may be undesirable.
Luckly for this test we’re using PHP. PHP has a method called mysqli_real_escape_string
$username = mysqli_real_escape_string($con, $_POST['username']);
This method escapes all special characters for use in a SQL statement.
An extra approach is to use prepared statements
$username = $_POST["Username"];
$password = $_POST["password"];
$con = new mysqli('localhost', user, password', 'database');
$sql = "select count(username) from tblUser where username = ? and password = ?";
$stmt = $con->prepare($sql);
$stmt->bind_param('ss', $username, $password);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($results);
$stmt->fetch();
echo $results . PHP_EOL;
The database user used to extract the data used in the web page should only have access to needed tables, in this case only tableOfUser and only select rights and no other.
Conclusion
- Check inputs, types ranges etc
- Use parameters
- Restrict database accounts to the bare minimum rights needed
- Avoid giving the end user database error messages