Widely regarded as a need-to-know skill for data analysts, SQL allows for quick access to stored information and is semantically easy to learn considering it uses declarative statements to pull records from databases.
According to a study that Dataquest ran, 55.8% of data analyst job ads posted on Indeed mentioned coding SQL queries specifically as a necessary skill. To put things into perspective, that’s over three times as often as Python was mentioned.
However, its popularity makes it a targeted attack point among hackers, which is why it’s important to make sure the SQL queries you support don’t introduce the risk of an SQL injection.
With this in mind, in this article, we’ll explain how you can use surgical SQL data selection queries without SQL injection vulnerabilities. But before we begin, let’s quickly cover the basics of SQL injections to put everything into context.
What Is SQL Injection?
An SQL injection is a type of code injection attack that can potentially destroy your database. The Akamai Media Under Assault report states that it’s one of the most common web hacking techniques. In fact, nearly 70% of all web application attacks between January 2018 and January 2019 were SQL injections.
These attacks take place when attackers place malicious code in SQL statements, primarily via web input. As a result, the application they’re interacting with performs unintended actions. These can range from displaying sensitive company data to private customer details.
SQL injections fall under three categories based on the methods they use to access data:
- In-band SQLi
- Inferential (Blind) SQLi
- Out-of-band SQLi
In the context of data analytics, an SQL injection can be used to return more data than expected. For instance, a data analyst might write a simple SQL query to select the first record of each group, in order to show account numbers and daily balances. An attacker, on the other hand, might modify the query to instead return all account numbers and balances stored in the database by simply replacing account numbers with statements that end with something like where account_number = 123 or 1 = 1 (which is always true).
SQL injections can have a detrimental impact on businesses of all sizes. A successful attack can lead to data leakage, unsanctioned modification of databases, or attackers gaining control of the entire database.
Using SQL Data Selection Queries Without Risking SQL Injection
Here, we’ll take a closer look at some of the ways you can use SQL data selection queries safely without risking SQL injection and share some best practices along the way.
Set up input validation
When web applications accept user input (for example, through a front-end form) and query databases, there’s always the possibility that unsanitized data will inject malicious code into the server. As a result, it can cause unintended and unauthorized actions to be performed.
Input validation is a process that determines whether the input provided by a user is allowed or not. More specifically, it checks for accepted input types, invalid characters, lengths, and formats. This way, only values that are verified pass the input validation and can be used as input. Input validation is an effective way to counteract SQL commands inserted into user input strings.
In addition to this, it’s also good practice to use regular expressions as whitelists for structured data. For example, if a user is asked to enter their zip code, it should match a zip code on the list. Similarly, if you need to get input from dropdown lists or radio buttons, you should apply checks to ensure the input data matches one of the available options.
In short, any data that a user provides (for example, a front-end form that allows freeform user input), needs to have input validation applied to it.
Use parameterized queries
Another popular way to use SQL data selection queries without risking SQL injection is by using parameterized queries. Put simply, it’s the process of pre-compiling SQL statements so that you only need to provide parameters to run it.
The benefit of using parameterized queries is that the database can recognize code and distinguish it from input data provided by a user. In terms of code, user input will be provided in quotation marks so as not to change the intent thereby preventing an injection.
Best practices suggest using parameterized queries with PHP 5.1 instead of MySQLi. As an added benefit, the code becomes more portable and readable.
Following our example from above, if a data analyst uses an SQL data selection query to display account details and balances for a specific user, the query is prone to SQL injection vulnerabilities.
To combat this, the data analyst can instead use a parameterized query by creating a prepared statement that only accepts integer values. If an attacker tries to enter an SQL statement in its place (e.g. 1 = 1 or x = x), the code will throw an exception. In simple terms, parameterized queries eliminate the possibility of unsanitized user input that can potentially change the intent of the SQL query.
Use stored procedures
Stored procedures are prepared SQL codes that can be reused over and over again. Instead of writing code every time, you can call a stored procedure to run the code. Stored procedures accept parameters, as well, which makes them perfect for protecting against SQL injections. Executing the statements over and over again will automatically parameterize them.
What this means is that data analysts can create stored procedures by grouping one or more SQL statements together. Since stored procedures are stored in and called directly from the database, they’re incredibly effective at protecting against SQL injection attacks.
Essentially, stored procedures minimize the risk of SQL injection by requiring the data analyst to write the SQL query beforehand, with markers for accepting parameters. This way, input data can safely be passed to them later thus allowing safe execution of the query without the risk of an SQL injection vulnerability.
Enforce least privilege
Data analysts should be granted as much access to databases as they need. This is the principle of least privilege and is a good practice for protecting against SQL injection attacks.
Following this principle, don’t connect applications to the database using an account that has admin access. This is because attackers can potentially exploit this to gain access to the entire system. To this end, you should take steps to ensure that each application has its own database credentials. In addition, make sure that those credentials have the absolute minimum rights required by the application to run.
Put simply, you need to avoid “ALL” grants. One way to do this is by providing read access to views instead of to database tables. This is an easy and effective way of preventing an attacker from compromising the entire system.
Conclusion
It’s safe to say that data is getting bigger day by day and SQL is here to stay. The good news is that there are several techniques you can employ to safely support SQL data selection queries without risking injection.
Start by setting up input validation to verify user input data. Use parameterized queries and stored procedures whenever possible as a standard. And, finally, you should be mindful of how implementing application accounts affects your entire system’s resiliency and enforce the principle of least privilege when assigning permissions.
Cyber Security Researcher. Information security specialist, currently working as risk infrastructure specialist & investigator. He is a cyber-security researcher with over 25 years of experience. He has served with the Intelligence Agency as a Senior Intelligence Officer. He has also worked with Google and Citrix in development of cyber security solutions. He has aided the government and many federal agencies in thwarting many cyber crimes. He has been writing for us in his free time since last 5 years.
