What is SQL Injection Attack and How to Prevent it
Check out all my other posts in My Technical Articles
--
A couple of weeks ago, I was pretty excited about my capability to design raw queries to get whatever data I need from MySQL. As a result, raw queries starts to build up quickly in my program.
What I didn’t know back then was the long existing internet security risk — SQL Injection Attack.
SQL Injection Attack is when a bad person take advantage of the build process of a SQL statement in order to get information they shouldn’t have access to, or delete table to shut down a running server.
How SQL Injection Attack Works
The most intuitive way to write a SQL query that consumes a dynamic input from a user in JavaScript, is to concatenate query string with user input.
However, this simple solution will expose our server to a bad person when it translates the Query String in JavaScript into a valid SQL statement.
The Normal Process We Naïve Developers Expect
The normal situation is that a user type in account and password normally, and then submit those data to our server. Our server then translates the Query String into a valid SQL statement like below:
The Malicious Process that Deceives We Naïve Developers
However, there are several ways for a bad person to type in the account to make final SQL statement works against our expectations:
Great, we now see the threat of SQL Injection Attack
How to Prevent SQL Injection Attack
1. Use Prepared Statement or Stored Procedure
Prepared Statement means the overall SQL statement is not dynamically parsed at runtime. We create prepared statement which specifies the location where we want it to be dynamic, and only the specified location will be swapped by dynamic input later. The dynamic input will never be parsed as SQL statement in prepared statement.
We use ? to specify the dynamic input, and dynamic inputs will be inserted in order, for example:
- MySQL Documentation reveals more details on Prepared Statement
- If you’re using Sequelize, then setting up query options will enable prepared statement
2. Always Validate User Input
Or we could validate user input directly in frontend, and again in backend, to ensure no malicious string is going into SQL query.
SQL Injection Attack has been here for decades that some developers might think it is a basic knowledge to posses. It might unintentionally decrease your candidacy to get into a backend developer interview. So make sure you’re using prepared statements to avoid this old school cyber attack when writing a dynamic SQL query.
