SQL injection vulnerabilities arise when you construct database queries unsafely, and untrusted data gets interpreted as a part of the SQL query structure. How to prevent SQL injection vulnerabilities? The best way to prevent SQL injection vulnerabilities is to use a framework that allows you to construct and parameterize queries safely. An ORM (Object Relational Mapper) is a good option. For additional security layers, validate all input and use a WAF (Web Application Firewall) product. A simple example Let's say we have a Java app that allows users to retrieve their documents by ID. We might do something like this: String query = + authContext.getUserId() + + request.getParameter( ) + ;
executeQuery(query); "SELECT * FROM documents WHERE ownerId=" " AND documentName = '" "docName" "'" So if the user ID is 25 and the URL is something like , then the query would be: https://www.example.com/documents/?docName=ABC123 * documents ownerId= documentName= ; SELECT FROM WHERE 25 AND 'ABC123' So far, so good. But what if the URL is https://www.example.com/documents/?docName=ABC123'OR'1'='1? Now we get the following query which returns all documents for all users (because 1=1 is always true): * documents ownerId= documentName= = ; SELECT FROM WHERE 25 AND 'ABC123' OR '1' '1' Oops. So how to avoid this? Object Relational Mappers Using Java as an example, using an ORM such as hibernate that implements the JPA (Java Persistence API) could look like this. First, define a model. { (strategy=GenerationType.AUTO) Long id; String documentName; Integer ownerId;
} @Entity public class Document @Id @GeneratedValue private private private Then, define a repository class. { ;
} @Repository public < , > interface DocumentRepository extends JpaRepository Document Long List<Document> findByDocumentNameAndOwnerId (String documentName, Integer ownerId) Finally, you can use the repository. Now you can fetch the documents like so: List<Document> docs = documentRepository.findByDocumentNameAndOwnerId(request.getParameter( ), authContext.getUserId()); "docName" The ORM will take care of handling all parameters safely. Now, suppose you want more control over your queries. In that case, many ORMs provide query builders that you can use, such as Hibernate Criteria API. https://spring.io/guides/gs/accessing-data-jpa/ https://docs.jboss.org/hibernate/orm/3.5/javadocs/org/hibernate/Criteria.html If you use Python, Django has a great ORM; if you don't use Django, sqlalchemy is an excellent option. https://docs.djangoproject.com/en/3.1/topics/db/queries/ https://www.sqlalchemy.org/ PHP has Doctrine. Just google for an ORM for your technology of choice. https://www.doctrine-project.org/ Warning ORM frameworks are not a silver bullet in two senses. The first is that they still have functionality for supporting raw SQL queries/query parts. Just don't use those features, and you're golden. The second is that ORM frameworks have vulnerabilities from time to time, just like any other software package. So follow other good practices: validate all input, use a WAF and keep your packages up to date, and you should be fine. Prepared statements Prepared statements are more of a legacy option and should be avoided because compared to ORM it has a significantly higher risk of human error. However, it still beats plain string concatenation. This approach could look like this: String query = ;
PreparedStatement ps = conn.prepareStatement(query);
ps.setString( , authContext.getUserId());
ps.setString( , request.getParameter( ));
ResultSet rs = ps.executeQuery(); "SELECT * FROM documents WHERE ownerId=? AND documentName = ?" 1 2 "docName" In theory, this is safe. However, in my experience, as the codebase grows larger, mistakes start to creep in. You only need one slip to completely vulnerable. Edge cases such as arrays (documentId IN ("foo", "bar")) are where the blunders often happen. So if you decide to go with this method, be very careful with it as you scale. Web Application Firewalls WAF products also are not a silver bullet, and should not be considered a security control per se. But they are an awesome additional security layer and usually quite effective against SQL injection attacks. An excellent open-source solution is to deploy Apache with ModSecurity CRS in front of your web application. https://modsecurity.org/rules.html Database Firewalls Depending on your database product and budget, you might want to consider giving database firewalls a try. I haven't tried one personally, but such things do exist. https://www.mysql.com/products/enterprise/firewall.html Conclusion SQL injection is at its heart a simple injection vulnerability. And like all injection vulnerabilities, you can prevent it by using a proper library/framework to construct the protocol, in this case, SQL. ORM is safer than prepared statements. And if you need low-level control of the queries, use a lower level ORM often referred to as a query builder. A WAF product can add a security layer, but you should never rely on it for your security. Previously published at https://www.appsecmonkey.com/blog/sql-injection-attack-and-prevention/

Apache

Fetch

Google

Subscribe to AppSec monkey now!

Read My Stories

Too Long; Didn't Read

Download free Tech Leaders Productivity Report!

What is an SQL Injection Attack? How To Prevent SQL Injection Vulnerabilities

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Windows Sticky Keys Exploit: The War Veteran That Never Dies

06/02/2018: Biggest Stories in the Cryptosphere

0-Days are on the Rise and that Means a Lot More Work for SOC Teams

Time Bombs Inside Software: 0-Day Log4Shell is Just the Tip of The Iceberg

The Noonification: How the Liver Transplant System Changed Forever (11/25/2023)

Windows Sticky Keys Exploit: The War Veteran That Never Dies

06/02/2018: Biggest Stories in the Cryptosphere

0-Days are on the Rise and that Means a Lot More Work for SOC Teams

Time Bombs Inside Software: 0-Day Log4Shell is Just the Tip of The Iceberg

The Noonification: How the Liver Transplant System Changed Forever (11/25/2023)

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps