SQL injection

SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.

Forms of SQL injection vulnerabilities

Incorrectly filtered escape characters
This form of SQL injection occurs when user input is not filtered for escape characters and is then passed into a SQL statement. This results in the potential manipulation of the statements performed on the database by the end user of the application.

The following line of code illustrates this vulnerability:

statement = "SELECT * FROM users WHERE name = '" + userName + "';"

This SQL code is designed to pull up the records of a specified username from its table of users. However, if the "userName" variable is crafted in a specific way by a malicious user, the SQL statement may do more than the code author intended. For example, setting the "userName" variable as

a' or 't'='t

renders this SQL statement by the parent language:

SELECT * FROM users WHERE name = 'a' OR 't'='t';

If this code were to be used in an authentication procedure then this example could be used to force the selection of a valid username because the evaluation of 't'='t' is always true.

While most SQL Server implementations allow multiple statements to be executed with one call, some SQL APIs such as php's mysql_query do not allow this for security reasons. This prevents hackers from injecting entirely separate queries, but doesn't stop them from modifying queries. The following value of "userName" in the statement below would cause the deletion of the "users" table as well as the selection of all data from the "data" table (in essence revealing the information of every user):

a';DROP TABLE users; SELECT * FROM data WHERE name LIKE '%

This input renders the final SQL statement as follows:

SELECT * FROM Users WHERE name = 'a';DROP TABLE users; SELECT * FROM DATA WHERE name LIKE '%';

Incorrect type handling
This form of SQL injection occurs when a user supplied field is not strongly typed or is not checked for type constraints. This could take place when a numeric field is to be used in a SQL statement, but the programmer makes no checks to validate that the user supplied input is numeric. For example:

statement := "SELECT * FROM data WHERE id = " + a_variable + ";"

It is clear from this statement that the author intended a_variable to be a number correlating to the "id" field. However, if it is in fact a string then the end user may manipulate the statement as they choose, thereby bypassing the need for escape characters. For example, setting a_variable to

1;DROP TABLE users

will drop (delete) the "users" table from the database, since the SQL would be rendered as follows:

SELECT * FROM DATA WHERE id=1;DROP TABLE users;

Magic String
The magic sting is a simple string of SQL used primarily at login pages. The magic string is

'OR''='

When used at a login page, you will be logged in as the user on top of the SQL table.

Vulnerabilities inside the database server
Sometimes vulnerabilities can exist within the database server software itself, as was the case with the MySQL server's mysql_real_escape_string() function[1]. This would allow an attacker to perform a successful SQL injection attack based on bad Unicode characters even if the user's input is being escaped.

Blind SQL Injection
Blind SQL Injection is used when a web application is vulnerable to SQL injection but the results of the injection are not visible to the attacker. The page with the vulnerability may not be one that displays data but will display differently depending on the results of a logical statement injected into the legitimate SQL statement called for that page. This type of attack can become time-intensive because a new statement must be crafted for each bit recovered. There are several tools that can automate these attacks once the location of the vulnerability and the target information has been established.[2]

Conditional Responses
One type of blind SQL injection forces the database to evaluate a logical statement on an ordinary application screen.

SELECT booktitle FROM booklist WHERE bookId = 'OOk14cd' AND 1=1

will result in a normal page while

SELECT booktitle FROM booklist WHERE bookId = 'OOk14cd' AND 1=2

will likely give a different result if the page is vulnerable to a SQL injection. An injection like this will prove that a blind SQL injection is possible, leaving the attacker to devise statements that evaluate to true or false depending on the contents of a field in another table.[3]

Conditional Errors
This type of blind SQL injection causes a SQL error by forcing the database to evaluate a statement that causes an error if the WHERE statement is true. For example,

SELECT 1/0 FROM users WHERE username='Ralph'

the division by zero will only be evaluated and result in an error if user Ralph exists.

Time Delays
Time Delays are a type of blind SQL injection that cause the SQL engine to execute a long running query or a time delay statement depending on the logic injected. The attacker can then measure the time the page takes to load to determine if the injected statement is true.

Preventing SQL Injection
To protect against SQL injection, user input must not directly be embedded in SQL statements. Instead, parameterized statements must be used (preferred), or user input must be carefully escaped or filtered.

Using Parameterized Statements
In some programming languages such as Java and .NET parameterized statements can be used that work with parameters (sometimes called placeholders or bind variables) instead of embedding user input in the statement. In many cases, the SQL statement is fixed. The user input is then assigned (bound) to a parameter. This is an example using Java and the JDBC API:

PreparedStatement prep = conn.prepareStatement("SELECT * FROM USERS WHERE USERNAME=? AND PASSWORD=?");
prep.setString(1, username);
prep.setString(2, password);

Similarly, in C#:

using (SqlCommand myCommand = new SqlCommand("SELECT * FROM USERS WHERE USERNAME=@username AND PASSWORD=HASHBYTES('SHA1', @password)", myConnection))
{
myCommand.Parameters.AddWithValue("@username", user);
myCommand.Parameters.AddWithValue("@password", pass);

myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader())
...................
}

In PHP version 5 and MySQL version 4.1 and above, it is possible to use prepared statements through vendor-specific extensions like mysqli[4]. Example[5]:

$db = new mysqli("localhost", "user", "pass", "database");
$stmt = $db -> prepare("SELECT priv FROM testUsers WHERE username=? AND password=?");
$stmt -> bind_param("ss", $user, $pass);
$stmt -> execute();

In ColdFusion, the CFQUERYPARAM statement is useful in conjunction with the CFQUERY statement to nullify the effect of SQL code passed within the CFQUERYPARAM value as part of the SQL clause.[6] [7]. An example is below.


SELECT *
FROM COMMENTS
WHERE COMMENT_ID =


Enforcing the Use of Parameterized Statements
There are two ways to ensure an application is not vulnerable to SQL injection: using code reviews (which is a manual process), and enforcing the use of parameterized statements. Enforcing the use of parameterized statements means that SQL statements with embedded user input are rejected at runtime. Currently only the H2 Database Engine supports this feature.

Using Escaping
A straight-forward, though error-prone way to prevent injections is to escape dangerous characters. One of the reasons for it being error prone is that it is a type of blacklist which is less robust than a whitelist. For instance, every occurrence of a single quote (') in a parameter must be replaced by two single quotes ('') to form a valid SQL string literal. In PHP, for example, it is usual to escape parameters using the function mysql_real_escape_string before sending the SQL query:

$query = sprintf("SELECT * FROM Users where UserName='%s' and Password='%s'",
mysql_real_escape_string($Username),
mysql_real_escape_string($Password));
mysql_query($query);

However, escaping is error-prone as it relies on the programmer to escape every parameter. Also, if the escape function fails to handle a special character correctly, an injection is still possible.

Find Out The Recovery Model For Your Database

You want to quickly find out what the recovery model is for your database but you don’t want to start clicking and right-clicking in SSMS/Enterprise Manager to get that information. This is what you can do, you can use databasepropertyex to get that info. Replace ‘msdb’ with your database name

SELECT DATABASEPROPERTYEX(‘msdb’,‘Recovery’)

What if you want it for all databases in one shot? No problem here is how, this will work on SQL Server version 2000

SELECT name,DATABASEPROPERTYEX(name,‘Recovery’)
FROM sysdatabases

For SQL Server 2005 and up, you should use the following command

SELECT name,recovery_model_desc
FROM sys.databases

What is deferred name resolution and why do you need to care?

DECLARE @x INT

SET @x = 1

IF (@x = 0)
BEGIN
SELECT 1 AS VALUE INTO #temptable
END
ELSE
BEGIN
SELECT 2 AS VALUE INTO #temptable
END

SELECT * FROM #temptable –what does this return

This is the error you get
Server: Msg 2714, Level 16, State 1, Line 12
There is already an object named ‘#temptable’ in the database.

You can do something like this to get around the issue with the temp table

DECLARE @x INT

SET @x = 1

CREATE TABLE #temptable (VALUE INT)
IF (@x = 0)
BEGIN
INSERT #temptable
SELECT 1
END
ELSE
BEGIN
INSERT #temptable
SELECT 2
END

SELECT * FROM #temptable –what does this return


So what is thing called Deferred Name Resolution? Here is what is explained in Books On Line

When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the syscomments system table.

When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the syscomments system table of the procedure and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.

In the resolution stage, Microsoft SQL Server 2000 also performs other validation activities (for example, checking the compatibility of a column data type with variables). If the objects referenced by the stored procedure are missing when the stored procedure is executed, the stored procedure stops executing when it gets to the statement that references the missing object. In this case, or if other errors are found in the resolution stage, an error is returned.

So what is happening is that beginning with SQL server 7 deferred name resolution was enabled for real tables but not for temporary tables. If you change the code to use a real table instead of a temporary table you won’t have any problem
Run this to see what I mean

DECLARE @x INT

SET @x = 1

IF (@x = 0)
BEGIN
SELECT 1 AS VALUE INTO temptable
END
ELSE
BEGIN
SELECT 2 AS VALUE INTO temptable
END

SELECT * FROM temptable –what does this return

What about variables? Let’s try it out, run this


DECLARE @x INT

SET @x = 1

IF (@x = 0)
BEGIN
DECLARE @i INT
SELECT @i = 5
END
ELSE
BEGIN
DECLARE @i INT
SELECT @i = 6
END

SELECT @i

And you get the follwing error
Server: Msg 134, Level 15, State 1, Line 13
The variable name ‘@i’ has already been declared. Variable names must be unique within a query batch or stored procedure.

Now why do you need to care about deferred name resolution? Let’s take another example
create this proc

CREATE PROC SomeTestProc
AS
SELECT dbo.somefuction(1)
GO

CREATE FUNCTION somefuction(@id INT)
RETURNS INT
AS
BEGIN
SELECT @id = 1
RETURN @id
END
Go

now run this

SP_DEPENDS ’somefuction’

result: Object does not reference any object, and no objects reference it.

Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won’t be 100% correct

SQL Server 2005 makes it pretty easy to do it yourself

SELECT specific_name,*
FROM information_schema.routines
WHERE object_definition(OBJECT_ID(specific_name)) LIKE ‘%somefuction%’
AND routine_type = ‘procedure’

How Do You Check If A Temporary Table Exists In SQL Server

How do you check if a temp table exists?

You can use IF OBJECT_ID(’tempdb..#temp’) IS NOT NULL Let’s see how it works


–Create table
USE Norhtwind
GO

CREATE TABLE #temp(id INT)

–Check if it exists
IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
BEGIN
PRINT ‘#temp exists!’
END
ELSE
BEGIN
PRINT ‘#temp does not exist!’
END

–Another way to check with an undocumented optional second parameter
IF OBJECT_ID(‘tempdb..#temp’,‘u’) IS NOT NULL
BEGIN
PRINT ‘#temp exists!’
END
ELSE
BEGIN
PRINT ‘#temp does not exist!’
END



–Don’t do this because this checks the local DB and will return does not exist
IF OBJECT_ID(‘tempdb..#temp’,‘local’) IS NOT NULL
BEGIN
PRINT ‘#temp exists!’
END
ELSE
BEGIN
PRINT ‘#temp does not exist!’
END


–unless you do something like this
USE tempdb
GO

–Now it exists again
IF OBJECT_ID(‘tempdb..#temp’,‘local’) IS NOT NULL
BEGIN
PRINT ‘#temp exists!’
END
ELSE
BEGIN
PRINT ‘#temp does not exist!’
END

–let’s go back to Norhtwind again
USE Norhtwind
GO


–Check if it exists
IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
BEGIN
PRINT ‘#temp exists!’
END
ELSE
BEGIN
PRINT ‘#temp does not exist!’
END



now open a new window from Query Analyzer (CTRL + N) and run this code again

–Check if it exists
IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
BEGIN
PRINT ‘#temp exists!’
END
ELSE
BEGIN
PRINT ‘#temp does not exist!’
END


It doesn’t exist and that is correct since it’s a local temp table not a global temp table

Well let’s test that statement

–create a global temp table
CREATE TABLE ##temp(id INT) –Notice the 2 pound signs, that’s how you create a global variable

–Check if it exists
IF OBJECT_ID(‘tempdb..##temp’) IS NOT NULL
BEGIN
PRINT ‘##temp exists!’
END
ELSE
BEGIN
PRINT ‘##temp does not exist!’
END

It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)

–Check if it exists
IF OBJECT_ID(‘tempdb..##temp’) IS NOT NULL
BEGIN
PRINT ‘##temp exists!’
END
ELSE
BEGIN
PRINT ‘##temp does not exist!’
END

And yes this time it does exist since it’s a global table

Finding all data types in user tables

This script queries sys.columns to get the entire list of columns and tables existing in the current database, then maps the columns datatype with a name from sys.systypes. The where clause filters the results for user created databases, less 'sysdiagrams', or you can use the commented out where clause to target a specific table.

This is a great way to hunt down various data types and make sure different development teams are on the same page and don't do silly things like having the data types on their tables not matching other tables and causing frustrations in forgetting to cast the values.


select object_name(c.object_id) "Table Name", c.name "Column Name", s.name "Column Type"
from sys.columns c
join sys.systypes s on (s.xtype = c.system_type_id)
where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')
-- where object_name(c.object_id) in (select name from sys.tables where name like 'TARGET_TABLE_NAME')

Increment a string

It's good to create a serial number to tickets, or another serie from data type character.

declare @litere nvarchar(3)
declare @litera1 char(1)
declare @litera2 char(1)

set @litere='AQ'

select @litera1=substring(@litere,2,1)
select @litera2=substring(@litere,1,1)

if @litera1='Z'
begin
set @litera1='A'
set @litera2=char(ascii(substring(@litere,1,1))+1)
end

else
set @litera1=char(ascii(substring(@litere,2,1))+1)

select @litera1, @litera2
set @litere=@litera2+@litera1
select @litere