What is MySQL Injection and How to Prevent them?

MySQL injection is a security problem in MySQL. It is one of the most common hacking techniques for any database.

SQL injection happens when we are trying to receive some sort of user input from the user interface, say for example a username and instead of the username the user sends a SQL statement. If we execute this SQL statement into our database without the proper checks, it can destroy database.

Next we will look at all the Types of SQL injections

MySQL Injection based on 1=1

Assume we have a HTML page with one text box for User id and we are using a function called getUserId() to fetch the information from the UI and then select/insert/update the Database.

In the User Id you are expected to input a text or a String. However instead of a username, a malicious user inputs the string :

110 OR 1=1

Assume that in our function, the SQL statement is formed like this:

userID = getUserId(“user_id”);

selectStmt = "SELECT * FROM Users WHERE UserId = " + userID;

Now with the input that the user has put in, the SQL statement becomes:

SELECT * FROM Users WHERE UserId = 110 OR 1=1;

Now in SQL 1=1 is Always True, so the above statement when triggered in the database, will return all the records from the Users.

Now if the Users table stores the passwords as well as the user ids, then the malicious users will have access to all this inofomation.

MySQL injection based on OR “”=””

Consider the same scenario where we are to insert a Username. Instead of just a username, a malicious user may type the input as

" or ""="

Assume that in our function, the SQL statement is formed like this:

userID = getUserId(“user_id”);

selectStmt = "SELECT * FROM Users WHERE UserId = " + userID;

So, the statement with the input becomes:

SELECT * FROM Users WHERE UserId = “” or ““=””

The clause OR “”=”” will always return True, the above query will return all the records form the users table which may also include the passwords.

How to Prevent SQL Injection:

To prevent SQL Injection, we need to verify all the inputs before they are sent to the database. Most programming languages that we use to build the frontend have inherent validations and also rules that we can set for Username and password etc. We need to have all such validations in place to prevent malicious code and SQL statements reaching the database.