SQL injection is a technique in which attackers insert malicious code into strings fields of an application (Web or not) that are later delivered to an instance of SQL Server for parsing and execution.
They can use it to gain unauthorized access to your sensitive data like customer information, personal data, business secrets, and more. Most times, he can change or delete this data, causing persistent modifications to the application’s content or behavior.
In this post, you will see how SQL injection works and how to prevent an attack.
How common are SQL injections?
The SQL Server injection represented 52% of all attacks in November 2019!
SQL Injection attacks are one of the oldest, most prevalent and maybe most dangerous web
application vulnerabilities from nowadays.
Contrast Labs’ security report shows that the most common attack types were SQL Injection, Cross-Site Scripting (XSS), and Path Traversal for the third month in a row.
Bobby drop tables and how SQL injection is done?
The example below is an old and popular comic about SQL Injection, a simple way to understand the concept, from xkcd.com, published in November 2008 (that’s right, that long ago!).
Using it as an example, if they used the name in a variable $sName, then a possible (simplified) version of the code in the school’s application probably looks something like:
As you can see, this is a very bad and naïve way to add text input into a query.
After the value from variable $sName, which is Robert’); DROP TABLE STUDENTS; –), past to query, the final result that will be executed against the SQL server will look like:
These are two valid queries separated by the statement terminator (semicolon):
- Add a new record to the Students table with a Name value of ‘Robert’;
- Delete the Students table;
So, the second query has been injected into the first.
How to prevent SQL injection?
If injected SQL code is syntactically correct, tampering cannot be easily detected programmatically. However, you can use the following best practices for preventing SQL injection.
Validate all data inputs
Probably the most important to preventing SQL injection is to validate any data that a user can provide.
No matter the origin of the input, whether via a textbox on a web form, API, or other application, it needs to be cleansed and validated.
This process will check user input for invalid characters, unacceptable length, or any other abnormalities before processing or storing it on any production systems.
There are many ways to validate the data. With ASP.Net, for example, use the attributes of the component like Regular Expression to validate the client-side (using JavaScript and jQuery) and class attributes/methods in the server-side (when a user has JavaScript disabled).
Prepare statements using type-safe SQL parameters
This allows the database to distinguish between code and data, regardless of what user input is supplied.
Parameterized queries force the developer to first define all the SQL code and then pass in each parameter to the query later.
Practically all object-oriented programming languages including Java, Python, Cold Fusion, and Classic ASP, support parameterized query interfaces.
With .NET, you can use parameterized queries like SqlCommand() or OleDbCommand() with bind variables. For example, all you have to do is pass the parameters to the query using the Parameters.Add() call as shown here.
Stored procedures are more rigid than the application code. They accept a known set of parameters and return predictable results. Always use parameterized input with stored procedures, If you cannot, and need to use dynamic SQL, you can still use parameters, as shown in the code above.
Review the code for SQL injection
Attackers use error messages to learn more about your database. Set your error message to tell that something goes wrong and encourage users to contact the technical support team.
Review LIKE comparisons carefully and ensure that the input string can’t pass through regex modifiers or unfiltered character that might allow a user to return results that are not intended for them.
Educate developers about the SQL risks and protection methods. Ensure that developers are familiar with application-specific threats and data sources. Write a set of coding standards that expect and protect common use cases for SQL database queries.
Create or customize automated source code scanning tool rules to check for vulnerabilities as code is being written.
Limit Privileges
Use an account with limited permissions to restrict damages with SQL Injection.
Do not share Logins, it makes harder to identify the source of a connection and is dangerous. Different applications should use different logins, including SQL Server services (SQL Server, SSIS, SQL Server Agent, and SRSS.) These logins should also be distinct from those used for other servers, services, and file shares.
The sysadmin role should be limited to those people whose job it is to administer a server. Also, the db_owner security role should be reserved for the rare use-cases in which an operator needs full database control.
Applications should not need either of these roles and should be able to operate within permissions to read, write, and execute to a specific database or set of databases.
Explore advanced threat protection alerts for your database in the Azure portal
If you are running your application on an Azure SQL Database, so Advanced Threat Protection automatically detects anomalous activities indicating unusual and potentially harmful attempts to access or exploit databases.
It can trigger two SQL Injection alerts. First, when an application generates a faulty SQL statement in the database. This alert may indicate a possible vulnerability. Second, when an active exploit happens against an identified application vulnerability to SQL injection. This means the attacker is trying to inject malicious SQL statements using the vulnerable application code or stored procedures.
To Explore the alerts for your Azure SQL database in the Azure portal, just click Advanced Threat Protection alert to launch the Azure Security Center alerts page and get an overview of active SQL threats detected on the database.
Conclusion
The SQL Injection attacks continue to grow and will continue to be a major security concert for years to come.
It’s all too easy to leave your system vulnerable to these attacks. Any application that queries a database is a potential target. So, any software developer needs to be aware of how it can put sensitive data at risk. When writing code remembers to validate and sanitize all user input. Always handle data carefully, monitor, and respond to security vulnerabilities throughout your organization.
By following these best-practices above, into a multi-layered approach if they circumvent one defense, you will still be protected.
Let our expert SQL Server Security team handle the technical complexities, so you can focus on driving your business forward with confidence.
