What is SQL Injection (SQLi)? How to Prevent It?

SQL injection can not only occur in MySQL but also in any other database. It is a common hacker attack and very popular in MySQL because several public databases on the web use MySQL.

Basically, in this, the hackers inject malicious code into text boxes. For example; you have a sign-up form on the web. The below code will insert the registration data in a table:

However, the hacker can inject malicious code in the password textbox:

Value;drop table mytable;

In the above example, the hacker is trying to add malicious code to drop a table named mytable.

So, you need to be careful with your forms, especially with the input forms where you have to submit data. This is because hackers can access your passwords or delete critical information.

How to Prevent SQL Injection Attacks?

Here are some recommendations that you can follow to avoid SQL injections.

Restrict User Permissions

It is a rule of thumb to restrict the permissions as much as possible. If the user requires only insert permissions in a specific table to log in and provide personal information, you can provide insert permissions only and remove select permissions to other tables. Make sure that the user does not have permission to system tables or system functions. Only provide minimum permissions required.

Use Stored Procedures instead of Plain Queries

Creating and granting access to only a specific stored procedure instead of providing access to execute SQL statements can reduce the risk to execute SQL injections.

Your text boxes in the application will fill some input variables in the stored procedure. If the hacker tries to inject malicious code, this will not be possible because the variable will have invalid values.

The following example shows how to generate a stored procedure in MySQL.

DELIMITER //
CREATE PROCEDURE proceduresample(IN vname VARCHAR(55), IN vlastname VARCHAR(60), IN vlogin VARCHAR(30), IN vpassword VARCHAR(50))
BEGIN
    INSERT INTO users (name, lastname, login, password)
    VALUES (vname, vlastname, vlogin, vpassword);
END//
 
DELIMITER ;

The procedure name is proceduresample and it receives 4 variables:

• Vname
• Vlastname
• Vlogin
• Vpassword

The delimiters are used in MySQL to define where the stored procedure begins and where it ends.

Once you have your own stored procedure, you can call it:

CALL proceduresample('John', 'Rambo', 'jrambo', 1354$%$$sword');

This code will insert data in the user’s table. If the hacker tries to make a SQL injection, the attack will fail.

The following code shows how this would work.

CALL proceduresample('John', 'Rambo', 'jrambo', 1354$%$$sword'; drop table users);

Now, you can try to drop the table users with this injection:

drop table users

If you try to run the code, you will get an error message similar to the below:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';drop table t1')' at line 1 

It means the injection will fail. 

Validators in the User Interface

You can also avoid SQL injections by adding validators in the code, when inserting data in the text boxes. The following example shows a validator to avoid strange characters in a textbox:

private bool checkStrangeCharacters(string textbox)
{
    // list of characters avoided
    List \string disallowed = new List \string() { ";","@" , "*/" };
 
    // Use a foreach to validate
    foreach (string i in disallowed)
    {
        if (input.Contains(i))
        {
            return false;
        }
    }
 
    return true;
}

The below example shows how to validate it in PHP.

function validateInput($textbox) {
    // list of characters avoided
    $avoided = array(';', '@', '/*', '*/');
 
    // Use a foreach to validate
    foreach ($avoided as $a) {
        if (strpos($input, $a) !== false) {
            return false;
        }
    }
 
    return true;
}

You can also do the same in JavaScript.

public boolean checkStrangeCharacters (String textbox) {
    // list of characters avoided
    String[] avoided = {";", "@", "/*", "*/"};
 
    // use the for loop to check
    for (String a : avoided) {
        if (input.contains(a)) {
            return false;
        }
    }
 
    return true;
} 

What if the database gets corrupted?

Sometimes, the database gets corrupt after applying SQL injections. In such a case, you can use a third-party MySQL repair tool, such as Stellar Repair for MySQL to repair the corrupt or damaged database. This software can repair MySQL or MariaDB databases. It can be used in Windows environment or CentOS Red Hat and Ubuntu. It allows you to repair and recover the database. It also saves the data in CSV, HTML, or XLS (Excel) format.

Conclusion

Above, we have explained SQL injection in detail and how to prevent SQL injection attack. You can prevent SQL injection by using stored procedures with variables. Another way is to add code to validate the input and check some strange characters.

In case the database gets corrupted, you can use Stellar Repair for MySQL to fix the database (MySQL or MariaDB) and recover all the data.