How To Avoid SQL Injection Attacks
In many ways, the popularity of programming languages such as PHP in web development are to blame for the prevalence of SQL injection attacks.
Many prominent websites, such as Facebook, were originally developed using PHP. This language is one in which the SQL injection attack has been a significant problem, but it’s far from the only language to have this type of vulnerability. Essentially any relational database management system with user-input options can fall prey to an SQL injection without taking secure coding.
Before we move onto how to avoid SQL Injection Attacks, it’s important to understand exactly what they are and how they work.
Nearly every website, program, app and bit of software that you use has a database or two that is absolutely essential to the whole thing. Search engines are a perfect example of such a database. Under normal circumstances, you enter the terms you wish to search the database for, or enter the data that you want to be saved. No problem.
However, in SQL injection attacks, the field is used to enter a malicious payload.
Here’s an example of code that would be used to login to a website:
# Define POST variables uname = request.POST['username'] passwd = request.POST['password'] # SQL query vulnerable to SQLi sql = “SELECT id FROM users WHERE username=’” + uname + “’ AND password=’” + passwd + “’” # Execute the SQL statement database.execute(sql)
Everything here is fairly normal. The user would enter their username and password into the appropriate fields, the code would check this against the database and if everything matched, then the user would be authenticated and granted access, such as in the below example.
SELECT * FROM Users WHERE username ="John Doe" AND password ="myPass"
In SQL injection attacks, rather than entering a simple string of characters to be checked against the database, the code is simply continued.
Rather than entering John Doe into the username field and myPass into the password field, you could enter this instead:
” or “”=”
” or “”=”
Which would be read by the database as:
SELECT * FROM Users WHERE username ="" or ""="" AND password ="" or ""=""
Since this is a perfectly valid piece of code for our database, it will simply return ALL entries from Users, as the statement ” or “”=” is always true.
So how can you avoid something like this?
The easiest way is to use prepared statements with parameterized queries, which will allow the user entry to be treated as a string rather than as executable code, regardless of what is typed.
Here’s how this could look in PHP:
$stmt = $dbh->prepare("INSERT INTO Customers (CustomerName,Address,City) VALUES (:nam, :add, :cit)"); $stmt->bindParam(':nam', $txtNam); $stmt->bindParam(':add', $txtAdd); $stmt->bindParam(':cit', $txtCit); $stmt->execute();
There are many other ways to prevent SQL injection attacks, but by always building parameterized queries into the code, you’re going to be much more secure.