In this post, we will advise steps to prevent your site from SQL injection. We have also discussed it technically on our other blog about prevent SQL injection.
SQL injection is a hacking technique that was discovered more than fifteen years ago and is still proving to be devastatingly effective today, remaining a top database security priority. It was used in the run-up to the 2016 U.S. presidential election to compromise the personal data of 200,000 Illinois voters, as well as in high-profile attacks against organizations such as Sony Pictures, PBS, Microsoft, Yahoo, Heartland Payment Systems, and even the CIA.
SQL, or Structured Query Language, is the command-and-control language for relational databases such as Microsoft SQL Server, Oracle, and MySQL. In modern web development, these databases are often used on the back end of web applications and content management systems written in PHP, ASP.NET or other scripting languages – meaning that both the content and behavior of many websites is built on data in a database server.
A successful attack on the database that drives a website or web application, such as a SQL injection login bypass attack, can potentially give a hacker a broad range of powers, from modifying web site content (“defacing”) to capturing sensitive information such as account credentials or internal business data. A SQL injection commands list is essentially the same as a list of database commands, include potentially catastrophic ones such as DROP TABLE.
Steps to prevent SQL injection attacks
The good news is that there actually are a lot of things that website owners can do to prevent SQL injection. Although there is no such thing as a foolproof solution in network security, formidable obstacles can be placed in the path of SQL injection attempts.
Here are ten steps you can take to significantly reduce the risk of falling victim to a SQL injection attack:
- Trust no one: Assume all user-submitted data is evil so use input validation via a function such as MySQL’s mysql_real_escape_string() to ensure that any dangerous characters such as ‘ are not passed to a SQL query in data. You should also sanitize everything by filtering user data by context. For example, email addresses should be filtered to allow only the characters allowed in an e-mail address, phone numbers should be filtered to allow only the digits allowed in a phone number, and so on.
- Don’t use dynamic SQL – don’t construct queries with user input: Even data sanitization routines can be flawed, so use prepared statements, parameterized queries or stored procedures instead whenever possible. But don’t forget that while stored procedures prevent some types of SQL injection attacks, they fail to protect against many others, so don’t rely exclusively on their use for your security.
- Update and patch: Vulnerabilities in applications and databases that hackers can exploit using SQL injection are regularly discovered, so it’s vital to apply patches and updates as soon as practical. A patch management solution might be worth the investment.
- Firewall: Consider a web application firewall (WAF) – either software or appliance-based – to help filter out malicious data. Good ones will have a comprehensive set of default rules, and make it easy to add new ones whenever necessary. A WAF can be particularly useful to provide some security protection against a new vulnerability before a patch is available. A popular example is the free, open source module ModSecurity, which is available for Apache, Microsoft IIS, and nginx web servers. ModSecurity provides a sophisticated and ever-evolving set of rules to filter potentially dangerous web requests. Its SQL injection defenses can catch most attempts to sneak SQL through web channels.
- Reduce your attack surface: Get rid of any database functionality that you don’t need to prevent a hacker taking advantage of it. For example, the xp_cmdshell extended stored procedure in MS SQL spawns a Windows command shell and passes in a string for execution, which could be very useful indeed for a hacker. The Windows process spawned by xp_cmdshell has the same security privileges as the SQL Server service account.
- Use appropriate privileges: Don’t connect to your database using an account with admin-level privileges unless there is some compelling reason to do so. Using a limited access account is far safer, and can limit what a hacker is able to do. For example, the code behind a login page should query the database using an account limited only to the relevant credentials table. This way, a breach through this channel cannot be leveraged to compromise the entire database.
- Keep your secrets secret: Assume that your application is not secure and act accordingly by encrypting or hashing passwords and other confidential data, including connection strings.
- Don’t divulge more information than you need to: Hackers can learn a great deal about database architecture from error messages, so ensure that they display minimal information. Use the “RemoteOnly” customErrors mode (or equivalent) to display verbose error messages on the local machine while ensuring that an external hacker gets nothing more than the fact that his or her actions resulted in an unhandled error.
- Continuously monitor SQL statements from database-connected applications: This will help identify rogue SQL statements and vulnerabilities. Monitoring tools that utilize machine learning and/or behavioral analysis can be especially useful.
- Buy better software: Make code writers responsible for checking the code and for fixing security flaws in custom applications before the software is delivered. SANS suggests you incorporate terms from this sample contract into your agreement with any software vendor.