How to Protect Your Website Against SQL Injection Attacks

SQL injection attack: key points

SQL injection attack is a serious website security threat. Attackers exploit vulnerabilities in website input channels to attack their databases, which may steal or change data, destroy functionality, or gain administrative access.

To prevent SQL injection attacks, do not trust user input and always verify whether there is a potential attack pattern. This includes not only text input, but also hidden input, query string parameters, cookies, and file uploads.

User input should be verified on the server side to ensure its type is correct and to eliminate any potential malicious commands. This can be achieved in a variety of ways, such as adding escape characters to input that may change SQL commands, or replacing user input in SQL commands with command parameters.

It is crucial to regularly check the website code for potential vulnerabilities, and it is also important to be prepared for SQL injection attacks. Measures to limit potential damage include avoiding the use of administrator rights, encrypting sensitive data, and not storing sensitive data unless absolutely necessary.

Other protection measures include: using a separate connection for read and write database operations, limiting user account access to a specific IP address, using Windows authentication model in MS SQL Server, and using strong algorithms such as SHA-2 Password hash.

Thanks to Chris Lienert and Guido Tonnaer for their help reviewing this article.

Among all attacks against websites, SQL injection is the most dangerous and common one, and has been used to cause actual damage to businesses and organizations over the past year. The program has been used to attack well-known organizations and companies, including TalkTalk, VTech, The Wall Street Journal and the U.S. government.

In short, SQL injection (also known as SQLi) exploits vulnerabilities in website input channels to attack the database of the web application backend, which stores the most sensitive and valuable information. Attackers can use this scheme to steal or tamper with data, hinder application functionality, and gain administrative access to the database server in the worst case.

Here is what you need to know about SQL injection and how to protect your website from its attacks.

How does SQL injection attack work

SQL injection attack is performed by sending malicious SQL commands to the database server through web requests. Any input channel can be used to send malicious commands, including form elements, query strings, cookies, and files.

To understand how it works, suppose you have a login form that accepts a username and password:

When a user enters his credentials and presses the "Login" button, the information will be published back to your web server and combined with SQL commands. For example, in PHP, the code looks like this:

$sql_command = "select * from users where username = '" . $_POST['username']; 
$sql_command .= "' AND password = '" . $_POST['password'] . "'"; 

The command will then be sent to the database server, and the generated dataset will determine whether the username and password correspond to a valid user account. The average user input "john" as username and "123456" as password (by the way, never use that password) will convert to the following command:

SELECT * FROM users WHERE username='john' AND password='123456' 

But if the user decides to try something else, such as the following:

The generated command will look like this, and it will always return a non-empty dataset:

SELECT * FROM users WHERE username='john' OR 1=1; -- ' AND password='123456' 

This code snippet may allow the user to bypass the login screen without having the correct credentials.

This is one of the easiest forms of SQL injection. With just a little more effort, the same user can insert a new user account, as well as delete or modify an existing user account. In the page where the results are displayed, the same scheme can be used to display records and information that were originally limited to view by ordinary visitors, or to change the content of records.

In more serious cases, an attacker can even completely destroy the server's operating system if connected to the database server through an administrator account (such as "root" in MySQL or "sa" in MS SQL Server). On a Windows server, this can manifest as an attacker performing an extended stored procedure, such as xp_cmdshell. In one case, an attacker exploited SQL injection vulnerability to create user accounts on an infected server, enable remote desktop functionality, set up SMB shared folders, and upload malware—except in fact messing up everything stored in the database .

How to protect yourself from SQL injection attacks

Since the user input channel is the primary medium of SQL injection attacks, most defenses involve controlling and censoring user input for attack patterns.

The following are some measures that can ensure user input is safe.

Do not trust user input

The primary rule regarding user input is "distrust and validate", which means that all forms of user input should be considered malicious unless it is proven that this is not the case. This not only works with simple input boxes, such as text areas and text boxes, but also with everything else – such as hiding input, querying string parameters, cookies, and file uploads.

Just because the browser's user interface does not allow users to operate input, it does not mean that it cannot be tampered with. Simple tools such as Burp Suite enable users to capture HTTP requests and modify anything, including hidden form values, before submitting them to the server. If you think you are smart enough to encode data via Base64, malicious users can easily decode, modify, and recode it.

Verify input string on the server side

Verification is the process of ensuring that the user provides the correct type of input and eliminating any potentially malicious commands that may be embedded in the input string. For example, in PHP, you can use mysql_real_escape_string() to escape characters that may change the nature of SQL commands.

The modified version of the login code mentioned above is as follows:

$sql_command = "select * from users where username = '" . $_POST['username']; 
$sql_command .= "' AND password = '" . $_POST['password'] . "'"; 

This simple modification will protect your code from attacks by adding escape characters() in front of single quotes deliberately added by malicious users.

A little note about validation: If you add a client verification function, it does a great job. But don't rely on it as a defense against SQL injection attacks. While client functions may make sending malicious input to your server harder, it is easy to circumvent with some browser tweaks and tools like the aforementioned ones. So you need to supplement it with server-side verification.

Some programming platforms (such as ASP.NET) include built-in features that automatically evaluate user input for malicious content when page postback. But hackers can circumvent them with enough skill and meticulousness, so you should still run user input through your own security checker. You will never be too cautious.

Use command parameters

A better alternative than escape is to use command parameters. Command parameters are defined by adding placeholder names in SQL commands, which will be replaced later by user input. ASP.NET provides a very intuitive and easy-to-use set of APIs for this purpose.

The following is code written in C# showing how to use command parameters to protect your website from SQL injection attacks:

SELECT * FROM users WHERE username='john' AND password='123456' 

You first create a SqlCommand object and use the @parameter_name paradigm into the command string, into which the user input should be inserted.

Then you create an instance of the SqlParameter object, inserting user input in it, instead of directly concatenating it with the command string.

Finally, you add the SqlParameter object to the Parameters collection of the SqlCommand object, which will replace the parameters with the provided input. ADO.net is responsible for the rest of the work.

In PHP, the equivalent is a preprocessing statement, which is more complex than its ASP.net counterpart. You can explore it here.

Explanatory conversion of your input

This trick works for weak-type languages ​​like PHP, which means you don't usually define data types for variables, and the language automatically handles conversions of different data types from each other.

Explanatory conversion can be used as a shortcut to escape input when non-string types are involved. So if you want the user to enter an int for the age parameter, you can use the following code in PHP to ensure the input is safe:

SELECT * FROM users WHERE username='john' OR 1=1; -- ' AND password='123456' 

Note that this code snippet only verifies the type of input, not its range. So you have to run other code to make sure that the user does not enter a negative age—or an unrealistic age, such as 1300.

In addition, another best practice is to avoid using single quotes in SQL commands involving non-string input. So, don't use the following code...

$sql_command = "select * from users where username = '" . $_POST['username']; 
$sql_command .= "' AND password = '" . $_POST['password'] . "'"; 

…It will be safer to use the following code:

SELECT * FROM users WHERE username='john' AND password='123456' 

How to eradicate SQL injection vulnerabilities

As a general practice, you should check the code for each page to see where you combine page content, commands, strings, etc. with sources that may come from the user. Checking your source code for vulnerabilities and security vulnerabilities should be an inherent part of the software development process.

You can also use scanning tools such as sqlmap to crawl the website's pages and find potential SQL injection vulnerabilities. In fact, hackers often use this tool to find and leverage SQL injection attack vectors on target websites, so why not use it to improve its security?

Your last line of defense

No matter how you strengthen your website's security, you have to be prepared for the day when SQL injection does happen. After all, as is well known in the cybersecurity industry, defenders have to win every battle, but hackers only need to win once.

Here are some tips to help you minimize damage when you become a victim of SQL injection.

Avoid Administrator permissions

Connecting your web application to a database server using a "root" or "sa" account is one of the most serious mistakes you can make. As I've already mentioned, a compromised administrator account can give hackers access to the entire system. Even non-admin accounts can cause damage, which can access all databases in the server, especially if the database server is shared between different applications and databases.

Therefore, it is better to use an account that has simple read and write permissions to a specific database located on the backend of the website, so if your website is hacked through SQL injection, the scope of damage will be limited to that single database Within range.

A more advanced approach is to use separate connections for code segments that read from or write to the database and further reduce the permissions and roles of each segment. For example, a list page (not modifying the database, but widely using search parameters) can be encoded using read-only connections to the database to further enhance the code's fault resistance.

In MySQL, security is improved by limiting access to user accounts to a specific IP address range (rather than the "%" model) to prevent access to damaged accounts from remote locations.

In MS SQL Server, I strongly recommend that you use the Windows Authentication Model, which will limit hackers' access to the database and ensure that they cannot use other channels to enter your database.

In addition, unless you plan to use some advanced features of SQL Server, it is better to set up Windows services to use a restricted account rather than a high-priced "local system" account. If the “sa” account is broken, this will minimize damage.

Encrypt sensitive data

Encrypt sensitive data in database. This includes passwords, security questions and answers, financial data, health information, and other information that may be useful to malicious actors. This will ensure that even if the hackers have your data in hand, they can’t exploit it immediately, which gives you time to discover vulnerabilities, plug vulnerabilities and take other reactions, such as forced password reset, which will ensure that the stolen data is under attack The person loses its value before deciphering.

If you are hashing your password, use strong algorithms like SHA-2, which will soon become the industry standard for password protection. MD5 and SHA-1 are outdated and can be reversed.

For other forms of encryption, please pay attention to the location where you store your keys and do not take a single bet. If the keys are right next to the encrypted data and the hacker will easily access them once the server is compromised, then there is no point in using encryption.

If not required, please do not store sensitive data

Whenever you store information in a database, consider how much damage it will cause if the information falls into the hands of a bad person and decide whether you really need to store it. The Ashley Madison hacker leaked the dark secrets and most private information of about 37 million people to the internet and caused some serious damage, partly due to the provider's failure to delete sensitive information from its database.

So the bottom line is, don't store sensitive information in the database unless you really have to. Even then, delete the message when it is no longer useful.

Final Thoughts

SQL injection has been around for decades and may continue to top the vulnerability rankings in the coming years. It only takes a few simple – but carefully calculated – steps to protect you and your users from it, and it should be one of your top priorities when reviewing the source code for security breaches.

The key to avoiding being the victim of the next large SQL injection data breach is: first, control and verify user input; second, prepare for "when", rather than "whether".

FAQs on Protecting Your Website from SQL Injection Attacks

What is the first step to protecting my website from SQL injection attacks?

The first step in protecting your website from SQL injection attacks is to understand what SQL injection is. SQL injection is a code injection technique that attackers use to insert malicious SQL statements into input fields for execution. This can lead to unauthorized access to sensitive data, data loss and even data corruption. Once you understand this, you can implement measures such as input validation, parameterized queries, and using stored procedures to protect your website.

How does input validation help prevent SQL injection attacks?

Input verification is a method of defining the syntax, content and logical values ​​of user input. By doing this, you can prevent attackers from inserting malicious SQL code into the input field of the website. This is because the input verification process will reject any input that does not meet the defined criteria.

What is a parameterized query? How do they prevent SQL injection attacks?

Parameterized query is a type of SQL query where a placeholder is used to represent the value, and the value itself is provided at execution time. This means that even if an attacker tries to insert malicious SQL code, it will be treated as a literal string, rather than part of the SQL command. This effectively prevents SQL injection attacks.

How does stored procedures help prevent SQL injection attacks?

Stored procedures are SQL statements stored in a database and can be called by an application. They can help prevent SQL injection attacks, as they do not allow direct access to the database. Instead, they use parameters that are not executed as SQL commands, thus preventing any injected SQL code from being executed.

What role does error handling play in preventing SQL injection attacks?

Correct error handling can help prevent SQL injection attacks by not revealing any information about the database structure or SQL syntax. This is because when an error occurs, the error message can provide an attacker with clues about the database structure or SQL syntax that they can then use to improve their attack.

How do I use the minimum permission principle to protect my website from SQL injection attacks?

The minimum permission principle means only providing user accounts or processes with the permissions necessary to perform their intended functions. For example, if a user account only needs to read data from the database, it does not need to grant it write access. This can help prevent SQL injection attacks by limiting what attackers can do when they try to exploit the vulnerability.

What role does regular updates play in preventing SQL injection attacks?

Regular updates are important to prevent SQL injection attacks, as they usually contain patches for known vulnerabilities. By keeping your software up to date, you can ensure you are protected from known vulnerabilities that may be exploited by SQL injection attacks.

How do I use a web application firewall to protect my website from SQL injection attacks?

Web Application Firewall (WAF) can help protect your website from SQL injection attacks by filtering and monitoring HTTP traffic between web applications and the Internet. It can identify and block SQL injection attacks by detecting malicious SQL code in HTTP requests.

What role does the intrusion detection system play in preventing SQL injection attacks?

Intrusion Detection System (IDS) can help prevent SQL injection attacks by monitoring network traffic and detecting suspicious activity. If IDS detects a potential SQL injection attack, it can alert administrators to even take steps to prevent the attack.

How do I use encryption to protect my website from SQL injection attacks?

Encryption can help protect your website from SQL injection attacks by making it harder for attackers to read sensitive data. Even if an attacker manages to exploit the SQL injection vulnerability, they still need to decrypt the data to use it.

The above is the detailed content of How to Protect Your Website Against SQL Injection Attacks. For more information, please follow other related articles on the PHP Chinese website!