SQL Server Integrity Violations allow attackers to inject malicious code into SQL statements and run it on the backend. This can open up SQL servers to ransomware attacks and data exfiltration, among other things. Because SQL integrity violations directly attack your databases – which house all of your data – they are a serious threat to the security of any company.
This article will discuss how SQL injection attacks work, explain why preventing SQL injection is so difficult, and provide tips on helping you prevent SQL server integrity violations within your environment.
How SQL injection attacks does work?
A common way for attackers to gain access to an SQL server is through a technique known as SQL injection. This occurs when an attacker inserts malicious code into the frontend of your website, which is then passed through to SQL and executed on the backend. Let’s see how this works with an example:
Consider you own a pizza delivery company and one way for customers to place orders is by visiting your website and clicking “Place Order”. When they click that button, it calls some JavaScript code on your webpage which passes some customer information into a new order in your database. Let’s also say that during this process, you had neglected to add any input validation (or sanitization) on the “Place Order” page.
When an attacker views this page they can make changes to the form fields and inject their own SQL code. Since we’re assuming you did not add any input validation on this page, the attacker can also execute that code and run whatever queries they like.
Note: This type of attack is more prevalent in web applications and mobile apps since many developers don’t make data validation a priority. However, it is important to note that these types of attacks can still affect regular desktop users too.
What makes preventing SQL injection difficult?
The most difficult challenge with preventing SQL server integrity violations is preventing them from occurring in the first place (i.e., input validation). Because of how SQL operates, there are several ways your data could become vulnerable if input validation isn’t added to every step the process. For example, consider the following SQL statement. This statement expects two input parameters to be passed in, and is commonly used for updating records:
This query will update the value of “Balance” when an input parameter of $Update Salary is given. However, notice that there is no validation on this page to ensure the user only passes the expected values into these fields (e.g., there’s nothing stopping a user from typing in 100000 instead of 10000). If we were to run this query with something like SELECT FROM Employees WHERE Login=’admin’ and Password=’test’, we could change someone else’s salary:
Attackers know this and therefore use this technique as a way to escalate their privileges within your system. They do so by sending SQL commands through the frontend of your website, which are then passed into the backend database and executed.
What are some ways I can prevent SQL injection on my site?
The best way to prevent SQL injection is by sanitizing user input before passing it into a SQL statement. Doing so ensures that any code an attacker may attempt to inject will be stripped out.
If you have a scripting knowledge base, here are some tips on how you could go about doing this for different technologies:
- JavaScript – use either the built-in JavaScript function call or new Function (parameters) {} syntax to run parameters as strings.
- PHP – use PHP’s mysql_real_escape_string () function which properly escapes any string being sent into a SQL statement.
- These scripting languages also have functions for enabling programmatic input validation which you should use to limit the values that are allowed.
- Also, it’s important to note that these same SQL injection techniques can be used to run commands directly on your backend servers, including deleting databases or granting administrator access. This is why it’s so important that you implement controls that prevent attackers from executing arbitrary code on your backend systems, perhaps through the use of application white listing/blacklisting technologies.
- MSSQLTips.com has a really good article going over the 5 integrity violations and how to prevent them with T-SQL code.
They discuss:
Missing Indexes, Insert or Update on a View that Violates UNIQUE Constraint, Triggers firing when they shouldn’t, Transactions not wrapping in the stored procedure and a very good additional tip, SQL Injection.
I have been going through this myself and it is definitely worth looking at as there are some great code examples that can be very helpful for preventing these problems from occurring.
Also, they have a great example on how to use T-SQL to prevent SQL Injection.
Once you have read this article go and check out how you can integrate T-SQL code in stored procedures with their new tool SQL Pro which includes a 30-day free trial.
Conclusion:
I hope this has been informative to anyone who wants to learn more about SQL injection and preventing it.