SQL Server Data Integrity Issues - Why Not Having That Backup Plan is A Bad Idea!‍

First off, I will say that the title is a bit of a 'click bait', but then again this blog has some pretty heavy duty articles on it and what is a blog for if not to get people to read your articles? 

Your SQL database base has turned bad or harmed. You don't have reinforcements. The best way to restore the database is to run the maintenance choice, which implies information misfortune risk. All things considered, running uprightness checks consistently (everyday or hourly) can identify information base debasement early, limiting information misfortune and vacation.

You have an exhaustive data set reinforcement procedure set up, permitting you to reestablish a SQL information base with no or negligible information misfortune and vacation. All things considered, you might consider running integrity really looks immediately seven days.

So let's look at some scenarios where data integrity issues can happen with SQL Server. 

Before we start though let's lay down some definitions so everyone reading this article understands what is being said:

SQL Injection –

This, in simple terms, is when an attacker inserts or 'injects' their own SQL code into your queries without you knowing about it. The original purpose of injection was to automate tasks through the insertion of dynamic SQL code. A lot of databases engines did not have input validation which allowed SQL injection attacks to happen. Nowadays most database engines do not allow this to happen in the first place (this is easy to say in hindsight after reading through some of the posts on http://sqlinjection.blogspot.com). The reason for this type of vulnerability is because when you write code that can receive dynamic input, you assume that all inputs are valid and will only execute what is expected, but if an attacker can inject their own SQL into your query they may be able to trick poorly written applications into executing unintended queries which could lead to data loss or data changes without authorization.

SQL injection is a web security weakness that permits an aggressor to impede the questions that an application makes to its information base. It by and large permits an aggressor to see information that they are not ordinarily ready to recover. This could incorporate information having a place with different clients, or whatever other information that the actual application can get to. As a rule, an assailant can alter or erase this information, making steady changes the application's substance or conduct.

In certain circumstances, an aggressor can raise a SQL injection assault to think twice about hidden server or other back-end foundation, or play out a forswearing of-administration assault.

An effective SQL injection attack can bring about unapproved admittance to delicate information, for example, passwords, Mastercard subtleties, or individual client data. Some high-profile information breaks lately have been the aftereffect of SQL injection attacks, prompting reputational harm and administrative fines. Sometimes, an aggressor can get a constant secondary passage into an association's frameworks, prompting a drawn out compromise that can go unrecognized for a lengthy period.

SQL Injection - How Common Is It?

There have been some reports on how common SQL injection is, such as the Trust Wave 2012 Security Report (http://go.trustwave.com/hubfs/Marketing_Library/Trustwave-2012-Security-Report.pdf) which says that SQL injection is still very common and up there with Cross Site Scripting in terms of most exploitable vulnerabilities. If you look at reports like this and reports from companies who do regular tests on web applications for security concerns they seem to indicate that quite a few websites are vulnerable. When we take into account how easy it can be to find sites open to attack using Google Dorking does it really come as a surprise?

A SQL Injection weakness might influence any site or web application that utilizes a SQL information base like MySQL, Oracle, SQL Server, or others. Lawbreakers might utilize it to acquire unapproved admittance to your delicate information: client data, individual information, proprietary advantages, licensed innovation, and that's only the tip of the iceberg. SQL Injection assaults are one of the most established, generally predominant, and most hazardous web application weaknesses.

SQL Injection - What Can You Do To Stop It?

So what can you do about stopping SQL injection happening on your web application? Well first off, do not use dynamic SQL queries to return data. 

There are two ways that dynamic SQL can be addressed in your code:

1) Use Stored Procedures - 

This is the easiest way to prevent SQL injection because whatever parameters are passed into a stored procedure it will always result in the same query being executed. How this works is all you need to change is what your search string contains or where you want your results coming back from. The downside of using this method is that if the temporary table space where your temporary table resides runs out of space then whoever owns the process running the code can generate significant load on the server which will slow things down for everyone else sharing it with others. If you have only one or two people making requests to the server then this should not be an issue for you.

2) Use Parameters in Your SQL Statement - 

The way this works is you need to make sure as a developer that your parameters are completely validated before they are added into the SQL string, only then can you feel safe about constructing your query to return data. This can be difficult because if the attacker knows what they are doing and it will also mean that every time you want to add or remove search criteria you need to go back and update all of the places where these queries exist in your code. The other downside with this method is that if there is a mistake anywhere in any part of your codebase related to the use of parameters the wrong results will be returned, this means that for every request to the server there can be multiple SQL queries being executed.

SQL Injection - Preventing It from Happening

The most popular way of preventing SQL injection is using input validation which checks all parameters before attempting to use them in a query. There are many good frameworks available now that will help with input validation but it still needs to be done manually by you or someone else who understands what they are doing. These days best practice dictates that all requests coming into your application should first go through a security gate so any vulnerabilities like SQL injection can be prevented from happening in the first place. Most frameworks today have these types of security gates built-in so if you are not sure about how to implement one or would like to use something that will save you time in the long run, there are plenty of good security frameworks out there.

Along with regular backups, it is also prudent to conduct vulnerability scans on a regular basis to identify any issues that bad actors can take advantage of. This is a topic that warrants an entire article on its own, but for a brief understanding take a look at Intruder’s top options when it comes to vulnerability scanning tools.

Conclusion:

I hope this blog post has helped you think about how to better protect your web application from SQL injection. It is important to have an open dialogue about how SQL injection works its harms and ways to protect yourself against it. There are plenty of articles out there that detail the technical possibilities behind the attacks but when it comes down to it, SQL injection is nothing more than a way of tricking an application into believing you are someone you are not.