What is SQL Injection?
SQL Injection is the number one exploit used by hackers to steal information and deface websites. SQL Injection is based on malicious altering of SQL statements from their intended format, by exploiting weaknesses of a client application that is used to create them. Most commonly, this takes place in Internet or intranet scenarios, where users are expected to enter data via text boxes presented through a Web-page interface, which, in turn is used to populate a dynamically formed query. The most classic case of such attacks (although certainly not the only one) involves a design, in which access to a database is controlled by the username and password pair stored in one of its tables. In the case of a match, a user is automatically redirected to another Web page, from which other SQL statements can be run.
Using SQL Injection, attackers can:
1. Change or delete website content
2. Steal user information, such as email addresses,
username & password, credit card details
3. Access databases connected to the website
An SQL Injection attack means that hackers are injecting SQL commands into form fields or URL parameters, which may be executed by the database of unprotected web servers.
SQL Injection example :
The dynamic SQL will put system at risk for SQL injection attacks, in which an attacker compromises the system's security by executing unauthorized code.
The following example show how a user exploits the dynamic SQL in a stored procedure. The Web page in this example lets the user specify searches for users based on their name. The Web page executes the following stored procedure, SearchUser:
CREATE TABLE [dbo].[Users](
[UserId] [int] NULL,
[Name] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
CREATE PROCEDURE SearchUser
DECLARE @SQL nvarchar(4000), @WhereClause nvarchar(500)
SET @SQL = N'SELECT * FROM dbo.Users'
SET @WhereClause = N' WHERE 1=1'
IF @name IS NOT NULL
SET @WhereClause = @WhereClause + ' AND name =
''' + @name + ''''
EXEC(@SQL + @WhereClause)
The parameters @user let users conduct wildcard searches by using LIKE patterns.
Using the SearchUser stored procedure, webuser issues the following command to return the generated queries and a list of users from the users table, which demonstrates the security danger of forming dynamic SQL without specific checks:
exec SearchUser 'avi';SELECT * FROM Users;
By injecting SQL code into the search parameters, an attacker can potentially perform unauthorized actions depending on the permissions of the user account, the Web page, or application executing the stored procedure.
How to secure SQL Server databases from SQL injection:
There are a few ways to protect your database against these kinds of attacks. First we need to lock down the database security using database security best practices. This involves setting up the database security with the lowest set of permissions possible. It also includes not using any table-level access to the tables. All access to the tables should be done through stored procedures, and those stored procedures should not include any dynamic SQL.
Without securing the application layer against SQL injection attacks, all bets are off. The data, as it comes into the database, is basically impossible to validate within the database. It needs to be validated at the application layer.
If you don't validate the data within the v_Input variable, then you leave yourself open to SQL injection attacks. If you don't validate the input, it allows the attacker to pass in a single quote, and a semicolon, which tells the SQL Server to end the value and the statement moving on to the next statement in the batch. You should do some basic validation and replace any single quotes within our variable with two single quotes.
Measures to avoid SQL injection :
Validate all input coming from the user on the server.
Avoid the use of dynamic SQL queries if there an alternate method is available.
Use parameterized stored procedure with embedded parameters. it is recommended you use parameterized queriesand filter all user input for non-alphanumeric characters.
Execute stored procedures using a safe interface such as Callable statements in JDBC or CommandObject in ADO.
Use a low privileged account to run the database.
Give proper roles and privileges to the stored procedure being used in the applications.
The most systematic method to do so is to set coding standards that require this to be done. If the code is
already written, a code review should be done to detect any vulnerabilities. It is also recommended you
look at some of the automated tools available for detecting these types of problems.
Even if you feel you have closed all known vulnerabilities, it is still in your best interest to prevent these
specific attacks by disabling some of SQL Server’s functionality. This is not practical if you are actually
using the functionality. Fortunately, the functionality we are looking to disable is not used often.