What is SQL Injection Attack and How to Prevent it

Check out all my other posts in My Technical Articles

Yu-Ming, CHANG (he/him)
2 min readSep 10, 2022
Photo by Quinn Buffing on Unsplash

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:

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.

--

--

Yu-Ming, CHANG (he/him)

I enjoy the positive mind flow when writing code to solve a problem. This is my journey to become a software developer, though now working as a product owner