Wordpress - Starting a blog/website - Cheat Sheet

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.

SQL Injection: A Simple Example
Take a simple login page where a legitimate user would enter his username and password combination to enter a secure area to view his personal details or upload his comments in a forum.

When the legitimate user submits his details, an SQL query is generated from these details and submitted to the database for verification. If valid, the user is allowed access. In other words, the web application that controls the login page will communicate with the database through a series of planned commands so as to verify the username and password combination. On verification, the legitimate user is granted appropriate access.

Through SQL Injection, the hacker may input specifically crafted SQL commands with the intent of bypassing the login form barrier and seeing what lies behind it. This is only possible if the inputs are not properly sanitized (i.e., made invulnerable) and sent directly with the SQL query to the database. SQL Injection vulnerabilities provide the means for a hacker to communicate directly to the database.

The technologies vulnerable to this attack are dynamic script languages including ASP, ASP.NET, PHP, JSP, and CGI. All an attacker needs to perform an SQL Injection hacking attack is a web browser, knowledge of SQL queries and creative guess work to important table and field names. The sheer simplicity of SQL Injection has fueled its popularity.

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.

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.

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 byte recovered. A tool called Absinthe can automate these attacks once the location of the vulnerability and the target information has been established.

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

Using Parameterized Statements
Parameterized statements use 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.

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 is rejected at runtime.

How to check for SQL injection vulnerabilities
Securing your website and web applications from SQL Injection involves a three-part process:

   1. Analysing the present state of security present by performing a thorough audit of your website and web applications for SQL Injection and other hacking vulnerabilities.
   2. Making sure that you use coding best practice sanitizing your web applications and all other components of your IT infrastructure.
   3. Regularly performing a web security audit after each change and addition to your web components.

Furthermore, the principles you need to keep in mind when checking for SQL Injection and all other hacking techniques are the following: “Which parts of a website we thought are secure are open to hack attacks?” and “what data can we throw at an application to cause it to perform something it shouldn’t do?”.

Checking for SQL Injection vulnerabilities involves auditing your website and web applications. Manual vulnerability auditing is complex and very time-consuming. It also demands a high-level of expertise and the ability to keep track of considerable volumes of code and of all the latest tricks of the hacker’s ‘trade’.

The best way to check whether your web site and applications are vulnerable to SQL injection attacks is by using an automated and heuristic web vulnerability scanner.

An automated web vulnerability scanner crawls your entire website and should automatically check for vulnerabilities to SQL Injection attacks. It will indicate which URLs/scripts are vulnerable to SQL injection so that you can immediately fix the code. Besides SQL injection vulnerabilities a web application scanner will also check for Cross site scripting and other web vulnerabilities.

Resources:

• http://en.wikipedia.org/wiki/SQL_injection
• http://www.unixwiz.net/techtips/sql-injection.html
• http://www.acunetix.com/websitesecurity/sql-injection.htm
• http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/
• http://www.acunetix.com/websitesecurity/sql-injection2.htm
  

Check Your SQL Server Identity

We expect developers to be able to create stored procedures, write moderately complex SQL statements, and even the occasional trigger where needed. One question I like to ask goes something like this:

"Let's take a scenario using SQL Server 2000 where I'll be passing in two variables (firstname and lastname) to a stored procedure. That procedure should insert them into a table called TEST that has columns also called firstname and lastname. Table TEST has a primary key column named ContactID which is an integer and is also marked as an identity column. How would I obtain and return the primary key for the row just inserted?"

Stop for a moment and see if you know the answer. Do you know how to create the stored procedure? Obtain the value? Return it to the calling application?

A fair question to ask me is - why is this important? For me, it's a fundamental test to see if someone has worked with data in anything beyond a trivial way. Take the standard order/order detail scenario - how do you insert the details if you don't have the primary key of the order? And while you may have had the luck (good?) to work on a system with natural keys, not every system uses them and identities are the most common way of solving key generation in SQL. More importantly, if you ever do work on a system where identities are used and you rely on @@Identity, you could get some very unusual results at some point in the future when someone adds an auditing trigger. It's not a deal breaker question, but it's an interesting one to lead them into a conversation about dealing with related tables.

I get a variety of answers and most of them are shall we say less than optimum. Almost everyone figures how to insert the values and knows to use either an output or return value, but almost everyone trips on the identity portion.

Wrong Answer #1 - Select max(contactid) from Test. This is wrong because it assumes that no one else will be inserting a row. I suppose you could make it work if you used the right isolation level, but doing that will most likely reduce your concurrency. It's also doing more than you need to.

Wrong Answer #2 - Select top 1 contactid from test order by contactid desc. This is wrong for the same reasons described above.

Wrong Answer #3 - Select the row back by querying on other data you inserted into the table, essentially saying that you inserted an alternative primary key made of one or more columns. This would work if your data supported it and guaranteed that those values were indeed unique. Still not a good idea.

Wrong Answer #4 - In this one they almost get it right. They suggest using @@Identity which will work of course (with caveats), but when I ask them if they are any concerns with this technique, I usually get one of the following:

- No, there are no concerns

- You have to query it quickly because it is a database wide setting and you have to get the value before someone else inserts a row into any table in the database.

- Yes, it retrieves the last identity value for the session which is usually the value you want, but could be incorrect if you had a trigger on TEST which inserted rows into another table that also had an identity column. In that case you'd get the identity value from that table instead of TEST (Note: this correctly describes the behavior @@identity exhibits).

Right Answer - Use Scope_Identity() because it's SQL 2000, use @@Identity in SQL 7, and return the result as an output parameter (return value typically reserved for error conditions). Using @@Identity represents a possible bug in the future if auditing were deployed and it used an identity column as well.

Now let's run a couple tests to prove that the right answer is really correct:

create database IdentityTest

use identitytest
create table TEST (ContactID int not null identity (1, 1), firstname varchar(100) null, lastname varchar(100) null)

insert into TEST Default Values
select @@Identity

This will return the value 1. Repeating it will return 2.

insert into TEST Default Values
select Scope_Identity()

This will return a value of 3.

Now let's start by proving that @@Identity can cause strange behavior. We'll create a history table first that has a new identity column, then we'll add an insert trigger to TEST.

create table TESTHISTORY (HistoryID int not null identity (1, 1), ContactID int not null, firstname varchar(100) null, lastname varchar(100) null)

create trigger i_TEST on dbo.TEST for insert as

set nocount on

insert into TESTHISTORY (ContactID, FirstName, LastName) select ContactID, FirstName, LastName from Inserted

Now let's test what happens:

insert into TEST Default Values
select @@Identity

Returns a value of 1. Inspecting TEST shows that the last row we inserted had a value of 4, the only row in TESTHISTORY has a historyid = 1.

insert into TEST Default Values
select Scope_Identity()

( Corrected as correctly pointed out by adk in the comments)
Returns a value of 5.
Inspecting TEST confirms this, and confirms we inserted a second row into TESTHISTORY. Now let's start testing what happens if someone else inserts a row into TEST while we're busily working away in our stored procedure. Using the existing connection, we execute the first part:

insert into TEST Default Values

If we check the table we see that we just inserted row 6. Now open a second connection and execute the same statement:

insert into TEST Default Values

Check the table reveals we just inserted row 7. Now go back to the original connection. We start with someone we know should return the "wrong" result and it does, the value 3.

select @@Identity

Now let's try scope_identity(). If all went well, it should return 6, not 7!

select Scope_Identity()

And it does, supporting the Right Answer detailed above. I know this is SQL trivia, the kind of stuff I think you shouldn't have to delve into, but if you're going to use the platform you have to know how it works. Take this back and quiz your developers, you'll be treating them to some professional development and you may save yourself a large headache one day too.

You could read the original article in this link:
http://www.sqlservercentral.com/columnists/awarren/checkyoursqlserveridentity.asp
Thanks to Andy Warren for this article