SQL Server Vulnerabilities

Even though we have said that there is no alternative, in reality, you could implement C2 security tracing. However note that the overhead is extraordinarily high in terms of resource (CPU, disk, memory, and so on) usage. Additionally, it is quite hard to configure a system where the trace occurs without the DBA having access to it.

Apart from a rogue DBA, the first real threat of unauthorized data access happens when the SQL Server security infrastructure is not properly configured. By this, we mean that a DBA doesn't know what they are doing and inadvertently end up leaving a hole in their server configuration. Closely related to this is the improper assignment of permissions in an application, such that a user has more permissions than they should be allowed to have.

The security settings of an out-of-the-box SQL Server 2000 installation are certainly better than those in SQL Server 7.0. However, still, a lot is left to be desired. In the summer of 2002, Microsoft finally woke up to SQL Server's security concerns. With virtually no exceptions, development at Microsoft came to a screeching halt for two months, as code and processes were reviewed line by line. The head engineer of Microsoft's SQL storage engine team, Peter Spiro, stated that 50 man- years of his programming staff were dedicated to security at this time. The results of these efforts can be seen in the SQL Server 2000 Service Pack 3. With the application of this service pack, SQL Server will become one the most secure database product ever produced by Microsoft. This covers both policy implementation, as well as source code review.

At the same time, we need to configure SQL Server properly. Numerous articles have been written about the setting up SQL Server properly. We will not cover these steps; instead we recommend that you refer to the Microsoft SQL Server 2000 Security white paper, it's available at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_security2000.asp

This document will give you the basic steps for securing your SQL Server. This said, we must ensure the following:

1. Do not allow a SQL Server to be exposed directly through port 1433 from the Internet; block port 1433 at the firewall. If you expose port 1433, you will be port-scanned, time and again, for known vulnerabilities. Even if you are up to date in applying the service packs , patches, and hotfixes, there is always the likelihood of a hacker finding some new vulnerability (for example, a buffer overflow).

2. Replace the SQL Server's default listen-on port for TCP/IP sockets (port 1433) with some other port and re direct your clients to this new port. This may help in forestalling an internal attack.

   To change the server's settings, perform the following steps. Run the Server Network Utility to connect to the server. Then, highlight the TCP/IP entry and click on the Properties button. Change the port number in the pop-up window and click on OK :

   

   After making the changes to the server, we need to configure the client. This can be done by running the Client Network Utility. In the Alias tab, click the Add button. In the pop -up window, against Server alias enter the name of your server. Then, click on the TCP/IP check box and specifically enter the new port number by unchecking the Dynamically determine port option:

   

3. Get the service packs, patches, and hoxfixes run through a development server and move into your production environment. This should be one of your highest priorities.

4. If at all possible, use integrated security instead of mixed-mode security for your applications.

5. Make the sa login's (that is, the one with SID of 0x01 ) password extremely hard and store it away securely. Only use it when it's absolutely necessary. It can have 128 characters in SQL 7.0 and above. Here's an example of one of our sa passwords:

   D899 ² 5B & pound ;dS59×zZAz OIG¼kN¾x jm ˆ ² ØJ»»J4UµAZCDkA 46 ‹ 9§¼lp§-r8OYi ‹ R§8qR 5§tWmß

   A script for making such random passwords is included in our code download; you can get it from http://www.apress.com.

6. If you use applications that require the use of the sa login, audit the use by putting an alert on the successful non-trusted logins (error 18454) with the letters sa . Here's the code snippet which accomplishes this:

   EXECUTE msdb.dbo.sp_add_alert @name = N'SA non-trusted login', @message_id = 18454, @severity = 0, @enabled = 1, @delay_between_responses = 8, @include_event_description_in = 5, @event_description_keyword = N'''sa''', @job_name = N'SA login email notify', @category_name = N'[Uncategorized]'
   

   This snippet fires a job which queries SYSPROCESS es for Windows 2000 login ID and application name, logs the usage in a table, and e- mails out the logins made by the sa user. In order for this job to work, you need to be auditing all successful SQL Server logins, but then again, you should be doing that anyway. This job and all the objects necessary for it are included in the code download.

7. We also suggest that all users having access to the sa login use a named pipes connection rather than the usual TCP/IP sockets connection. By making your users use named pipes, you will be able to positively identify their NT account with their connection. There is a small performance trade-off for the connection, but the vast majority of applications and servers will not suffer. In this way, you can also ensure that the operating system approves the security credentials of the users, before they get to SQL Server.

Permission Paths

Now, let's look at how a person gets access to the database objects, in terms of the following elements — Windows Users and Groups, SQL login IDs, database objects, database users, database roles, object ownership, and object permissions.

Windows Users and Groups

These are the user accounts and groups that users are assigned to in the operating system. Internally, a binary number, called Security ID or SID, identifies a user or a group. When a user logs into a Windows 2000 computer, they get a security token for their login session. A user in a group will have the SID of that group , on the security token. A user wanting to work with a particular SQL Server presents the token to the SQL Server for access.

The user and group accounts can be either at the domain level or at the local computer level; generally , the user accounts are at the domain level, since a local computer account would provide access only to that single computer. An exception to this is the service accounts for the SQL Server Service and the SQL Agent Service, which may be at the local computer level.

SQL Login IDs

These logins are present at the SQL Server level, with the data supporting them residing in the sysxlogins table of the master database. They can either be integrated logins with the operating system or standard logins that have no direct relationship with the operating system. If they are integrated, the 28-byte SID in SQL Server is obtained from either the domain or local computer by using the undocumented GET_SID function (we have discussed this function in Chapter 5). This SID directly corresponds to the SID of a particular user or group.

When a user logs into the SQL Server, with an integrated login, they present their token with their SID collection to the SQL Server for comparison with sysxlogins . They can have multiple permission paths since they could be identified with multiple SIDS. A stored procedure, xp_logininfo , shows all the permission paths for an integrated login.

If it is a standard login, the SID in sysxlogins is a self-generated 16-byte binary number. In this case, the password for the login is also stored in an encrypted form, in the password column of the sysxlogins table. Normally, by default the sysxlogins table and its password column for standard logins is only readable to members of the sysadmin role.

When users log in with a standard login, they have to present their password for comparison with the encrypted password stored in sysxlogins . An undocumented function, PWDCOMPARE() , compares the user's password with the encrypted password. The function does this by sending the supplied password through a one-way encrypting function. If the result matches the encrypted password, the function returns a 1, otherwise 0. Normally, the standard password is sent unencrypted to the server, unless the server has a certificate assigned to it in which case the password is automatically encrypted with the public key of the server certificate.

For more details refer to the section on Installing a SSL Certificate.

Database Objects

Apart from the little bit of configuration data that SQL Server stores in the registry, virtually all data is stored in tables. SQL Server has two types of tables – system tables and user tables. Other objects in the database allow customized viewing (through objects called views), improved sorting (through indexes), customized processing (through stored procedures and functions), limiting table usage (through constraints and defaults), and defining custom data formats (through user-defined data types). Three types of objects are used for customized processing – stored procedures, extended stored procedures, and functions. We'll refer to these collectively as procedures.

The basic record for each object is stored in a system table called sysobjects . The definitions for each procedure are stored in one or more records of the syscomments system table, in binary format. We'll talk more about the syscomments table later on in the section on Encrypting Stored Procedures.

Database User IDs

Database User IDs are how Windows login SIDs mapped down into the database level. The database user ID can be identified with a SID of a standard login, integrated user, or integrated group. The SID in the sysusers table of the database is the same as the SID of the sysxlogins table in the master database. Usually the names of the login and user ID are the same.

There are two special kinds of user IDs – the database owner or DBO and the guest user ID. The DBO has maximum and irrevocable rights in the database and will always be identified with one of the SID s in sysxlogins . In order for a login to have access to objects in a database, they must be assigned to a user ID in the database. However, there is one exception. If there is a guest user ID in the database, all server logins that do not have an explicit user ID, inherit the permissions assigned to the guest user ID.

Object Permissions

There are two types of permissions in a database – statement permissions and object permissions. Statement permissions allow you to do things like creating objects and backup databases. In this chapter, we will focus on object permissions. As the name implies they control permissions to use objects.

In T-SQL, a command to setup permissions has the following syntactical structure:

Action Permission ON object TO SYSUSER_Rec

In the following example, we use the GRANT action to allow the SELECT permission on the sysobjects object to a sysusers record named JERRY:

GRANT SELECT ON sysobjects TO JERRY

As we mentioned, object permissions include SELECT , UPDATE , INSERT , DELETE , EXECUTE , REFERENCES , and ALL . ALL includes every one of the others. Only the EXECUTE permission applies to stored procedures. If you use ALL for a stored procedure permission statement, it will apply the EXECUTE permission only. EXECUTE and REFERENCES apply to scalar valued functions.

There are three actions for managing permissions – GRANT , DENY , and REVOKE . GRANT allows you to exercise a particular permission on an object, while DENY keeps you from exercising it. REVOKE removes the effects of either a previously defined GRANT or DENY permission setting for an object. If a permission path places both the GRANT and DENY actions on a particular object for a user, the DENY action takes precedence.

Coming back to our above example of Jerry and the sysobjects table, if Jerry is a member of a group called Accounting with an Accounting group login and a corresponding user ID in the database, we can issue the following command:

DENY SELECT ON sysobjects TO ACCOUNTING

As a result, Jerry will not be allowed to execute SELECT on the sysobjects table because the DENY action override for the Accounting group user ID overrides the earlier GRANT action, which was directly applied to Jerry. In short – any DENY will always override a GRANT .

The sysuser record can be either a user ID whose SID is mapped to a SQL Server login or a database role.

Database Roles

The sysusers table in a database stores user IDs and roles. We have already talked about user IDs. Roles differ from user IDs in several ways. Firstly, the UID value for a role record in sysusers is always greater than 16384, while a user ID's UID value is 16383 or less. To see the roles in a database, use this query:

SELECT * FROM dbo.sysusers WHERE UID > 16383

Alternatively, you execute the system stored procedure sp_helprole :

EXEC

sp_helprole

Roles are also different from user IDs in the sense that roles are essentially containers. There is a system table called sysmembers that maps user IDs and other roles, as members of a role. Members are added to roles by using the sp_addrolemember procedure. Please note that, a role can be nested as a member in another role, but you cannot have circular nesting. If you have nested roles, you need to set up a hierarchical nesting structure, where a node cannot be a parent of any node above or up to itself in the hierarchy.

Microsoft designed security in SQL Server 7.0 and SQL Server 2000 with the idea that permissions would be assigned to roles and not to user IDs. By assigning permissions to a role, we can tremendously increase the flexibility of our database security structure. You can find more on this in the Putting it all Together section.

There is a special role called PUBLIC with UID 0. Every user ID in a database is automatically a member of the PUBLIC role, even without being defined in sysmembers . Finally, roles are different from user IDs; the SID column in sysusers for a user ID maps back to a SQL Server login in the sysxlogins table of the master database, the SID column in sysusers for a role is NULL .

For removing all public permissions, we can use the following script:

SET NOCOUNT ON -- We will loop through one record at a time SET ROWCOUNT 1 DECLARE @i INT, @cmd VARCHAR(1000) SELECT @i = 1 WHILE @i > 0 BEGIN SELECT @cmd = NULL -- u.name is the owner of object o.name SELECT @cmd = u.name+'.'+o.name FROM sysobjects o JOIN syspermissions p ON o.id = p.id JOIN sysusers u ON o.uid = u.uid WHERE p.grantee = 0 -- public grantee UID is 0 SELECT @i = @@rowcount -- the last time through @cmd IS NULL IF @cmd IS NOT NULL BEGIN SELECT 'Revoking PUBLIC perm on '+@cmd EXEC ('revoke all on '+@cmd+' to public') END END

Object Ownership

A record in the sysusers table, either a user ID or a role, always owns tables, stored procedures, views, and other objects in the sysobjects table. You can change an object's owner by using the sp_changeobjectowner procedure.

An fully named object consists of four parts :

1. Object name

   If there are no objects with the specified name owned by the current user, the DBO owner is assumed. If there are no objects owned by the current user or the DBO with the object name, an invalid object name error is returned. Generally around 97% of databases have all their objects owned by the DBO user ID.

2. Owner name

   If the owner name is left off, it's assumed to be same as the currently logged in user. This may lend a great deal of convenience, as the owner doesn't have to be explicitly named in an object call, but it is also a security hole.

3. Database name

   If the database name is left out, the object is assumed to be in the current database.

4. Server name

   If the server name is left off, the current server is assumed. This would normally be left off, except in cross-server calls.

A fully-qualified name generally has only the first three parts, for example, master.dbo.sysobject . If the database name is left out, the object is assumed to be in the current database.

However note that, this is a highly controversial decision. As we said earlier, almost 97% of databases have all their objects owned by the DBO user ID. It is a de facto standard. Using the DBO user ID may make your database susceptible to cross-database hacking, where the databases inadvertently have common owners and the security in the second database isn't tight.

Let's look at an example. First, create a TESTER login (with a blank password), we will use it to run a cross-database view. Before you go any further, make sure that the pubs and Northwind databases are owned by the same login. In our case, it is the sa login with a SID of 0x01 .

Look at the sysdatabases table and ensure that the SID s for the two databases are the same. In the first instance, the DBO will be the owner of both objects. In the second instance, the owner of both objects will be the APP role. First, log in through Query Analyzer, as a user with sysadmin rights for the server. Then run the following script to set up the test environment:

EXEC master.DBO.sp_addlogin tester EXEC pubs.DBO.sp adduser tester EXEC northwind.DBO.sp_adduser tester EXEC sp_defaultdb tester, northwind EXEC pubs.DBO.sp_addrole app EXEC northwind.DBO.sp_addrole app SELECT * INTO pubs.app.authors FROM pubs.DBO.authors USE northwind GO CREATE VIEW app.vw_authors AS SELECT * FROM pubs.app.authors GO CREATE VIEW DBO.vw_authors AS SELECT * FROM pubs.DBO.authors GO GRANT SELECT ON app.vw authors TO tester GRANT SELECT ON DBO.vw_authors TO tester

Next , login through Query Analyzer with the tester login. Remember, that it has a blank password. Now, run the following scripts on the Northwind database:

SELECT * FROM DBO.vw_authors

You will get the record set of the DBO.authors table in the pubs database. This illustrates the potential cross-database security hole of always using the DBO user ID, as an object owner. Now execute the following query, as tester , in the Northwind database:

SELECT * FROM app.vw_authors

You will get a SELECT permission denied error on the pubs.app.authors table. By not using the DBO as the object owner, you can forestall a common-owner cross-database hack. The requirements for this hack to occur across the common DBO are:

• We need two databases with a common login as owner

• In the first database, the login must be a user with very limited permissions

• In the second database, the same login must be a user satisfying at least one of these conditions:

  • CREATE VIEW statement permission

  • CREATE PROCEDURE statement permission

  • member of the db_ddladmin fixed database role

  • member of the DB_OWNER fixed database role

• The login creates a DBO -owned procedure or view in the second database that accesses or changes objects (tables) in the first database that the user shouldn't have access to

Incidentally, in an out-of-the-box installation, the pubs database meets these conditions as the ‘second database’, such that any login in the server could carry out this hack on a database with the same owner as the pubs database. In a default install, right-click the pubs database in SQL Enterprise Manager and click Properties . Look at the Permissions tab, and you will see that the guest user ID has CREATE VIEW and CREATE PROCEDURE permissions.

We have a feeling that Microsoft is really addressing this problem with its system tables, due to poorly implemented applications. After all, even if you make the user objects owned by the APP role in your databases, a hacker can still get a good picture of your schema through this security hole by querying cross-database the system tables. If you run the following query in SQL 2000 having Service Pack 1 or later installed, you will see what we mean:

SELECT [DESCRIPTION] FROM MASTER.DBO.SYSMESSAGES WHERE ERROR = 15355

This query will produce the following output:

"'sys' will be a reserved user or role name in next version of SQL Server."

Putting it all together

Now that you know what a login, user ID, role, object, object ownership, and permissions are, let's look into the correct way for setting up a permission chain for an ideal application. The following diagram shows this:

Figure 1

From the diagram, we have the following sequence:

1. Note first that the application uses integrated security.

2. Set up either a domain global group in Windows NT 4.0 domain SAM, or a global or universal group in a Windows 2000 Active Directory. Universal security groups are only available in Active Directory enterprises running in native mode. If you don't know what mode you are in, ask your domain managers. We prefer to prefix such groupnames with the letters SQL and then have a meaningful name without any spaces. It lets everyone know exactly what the group is for, and groups all our SQL groups in one area of the (management) GUI.

3. Add the relevant user accounts to the group that you created in step 2.

4. Run SP_GRANTLOGIN to give a SQL Server login to the group you created in step 2. We prefer to script everything out and save them; you never know when they'll come handy for disaster recovery or when you'll have to use them in another server. Plus, they serve as handy documentation. Keep the domain/groupname part for the login name. It keeps all the groups from a domain group together in the GUI.

5. Run SP_ADDUSER to give the login access to the relevant database. We usually strip off the domain names here and make the username just the groupname. Thus, almost all the usernames are SQL plus the "meaningful name without spaces".

6. Add a role to the database using SP_ADDROLE . The role name is the username minus the SQL prefix, that is, just the "meaningful name without spaces".

7. Add a role named APP to the databases if it didn't inherit such a role from your model database.

8. Create all your objects under the ownership of this newly defined role (that is, APP ).

9. Grant the minimum permissions necessary for the proper operation of the application to the role created in step 6. To make the application secure, all the data access should be through stored procedures.

10. If, for some reason, you have super users who need to control permission on your APP role objects then give them membership in the APP role. This would be the ONLY exception when we would give non- DBO users membership in the APP role.

Some Notes about this Sequence:

• This sequence will give you good indirection between users and roles. To quickly remove a role from an application, just remove the user IDs, representing groups, from the role membership. In this way, the assignments of the permissions to the role remain intact.

• When you move a database to another server, either by backing up and restoring or detaching, copying, and reattaching, the permissions will automatically get synchronized if logins are present for the relevant groups. If you install Service Pack 2 (Client Tools module) for SQL 2000, in SQL Enterprise Manager you can readily see orphaned User IDs from databases that don't have a corresponding login with the same SID .

• Contrary to Microsoft ‘recommended method’, we don't make use of local groups on the operating system of the SQL Server to indirect the domain global groups. If we do this, then when we move a database from a development to production instance or vice-versa, SID synchronization is a nightmare. Microsoft made this recommendation because it gives a valid one-to-many relationship solution between the local group and multiple domain global groups. We rarely operate SQL Server in multiple domain environments in NT 4.0.While this was valid in NT 4.0, with the introduction of universal groups in Windows 2000 Active Directory, this is a non-issue.

• This sequence applies only to non-system databases (not MASTER , MSDB , or TEMPDB ).

SQL Injection Attacks

SQL injection attack is the single biggest threat faced by SQL-based applications today, especially by those applications which are exposed to the Internet. SQL injection refers to the addition of malicious SQL code to legitimate SQL code, contained within an application. All SQL based DBMSs, and not just Microsoft SQL Server, are potentially susceptible to this attack, as they use SQL. It is a problem with the SQL language.

The Problem

The attack happens when an application processes user input that forms part of a SQL command. If the application builds executable SQL strings by using unfiltered input, it may be subject to malicious input. A good application builder always operates by the dictum, "never trust user input". By using escape characters, the malicious user can append unintended SQL commands to your intended SQL code.

Example 1

We'll use the pubs database to demonstrate the problem. Assume that we have an application that takes user input from a textbox called InputBox.Text . Now we need the code for generating the SQL executable code from user input. For this example, it could take one of two forms (the procedure AUTHORLOGIN is a nonexistent fictitious procedure that you could build) The VB code would look something like this:

"SELECT au_lname, au_fname FROM authors WHERE au_id = '" & _ InputBox.Text & "'"

or

"EXEC pubs.dbo.AuthorLogin '" & InputBox.Text & "'"

We'll go through the first example in detail; however, the second is an equally valid attack.

Let's set up the scenario. Imagine that you have a web application. The web application or COM+ proxy account under which SQL code is executed has broad rights in the pubs database. In the worst conditions, the proxy account has DBO or even sa rights in the SQL Server. Drill down in the object browser and open the jobs table in the pubs database.

In a default pubs instance, we will see that the table has 14 jobs. To confirm this, run the following SQL query:

SELECT * FROM jobs

Now, we will simulate a normal user of your web application by executing the following code in the Query Analyzer, on the pubs database:

-- Set quoted identifiers OFF so that double-quote work like in VB or -- VBScript code SET QUOTED_IDENTIFIER OFF DECLARE @InputText VARCHAR(100), @command VARCHAR( 1000) -- Imagine that the application is capturing the InputText.Text -- variable SELECT @InputText = "527-72-3246" SELECT @InputText AS 'InputText.Text' -- Now, imagine that app is VB or VBScript and piecing together a SQL -- command. The final command is represented by @command SELECT @command = "select au_fname, au_lname from authors WHERE au_id = '" + @InputText + "'" SELECT @command AS 'Executable SQL Command' -- Imagine that we pass the command to SQL: EXEC (@command)

If this query works, as intended, we will get Morningstar Greene as the output.

Now, we will simulate a malicious user's input into the @InputText variable. In the above example, instead of using 527-72-3246 , change the line to:

SELECT @InputText = "' ; INSERT INTO jobs (job_desc, min_lvl, max_lvl) VALUES ('Important Job',25,100) -- "

Notice how we first terminated the current string in our @command statement with ' (the single quotation mark) character. This will close the first statement (in the remainder of @InputText exists) and allows us to start a new statement that we can execute. Our new SQL statement, which is malicious, follows the ' . Finally, the -- (double dash) character string tells SQL to ignore the rest of the text, as a comment. In our case it causes the closing ' (single quotation mark) to be ignored, which would otherwise cause the final @command executable to generate a parsing error.

Substitution of the SQL injection code will make our sample code looks like (we'll leave out the previous comments for brevity):

SET QUOTED_IDENTIFIER OFF DECLARE @InputText VARCHAR(100), @command VARCHAR(1000) SELECT @InputText = "' ; INSERT INTO jobs (job_desc, min_lvl, max_lvl) VALUES ('Important Job',25,100) -- " SELECT @InputText AS 'InputText.Text' SELECT @command = "select au_fname, au_lname FROM authors WHERE au_id = '" + @InputText + "'" SELECT @command AS 'Executable SQL Command' EXEC (@command)

After executing this code, right-click and open the jobs table in pubs . The malicious job, which we have added through injection attack, is Important Job .

Example 2

Let's look at one more malicious example. Before running this, first make a new table in pubs by running the following command:

CREATE TABLE ATEST (testcol INT)

Now, change InputText to select a name from sysobjects . The top portion of your code will look like:

SET QUOTED_IDENTIFIER OFF DECLARE @InputText VARCHAR(100), @command VARCHAR(1000)

SELECT @InputText = "' ; SELECT TOP 1 NAME FROM SYSOBJECTS WHERE TYPE = 'U' ORDER BY NAME -- "

For this example, consider that your web application returns a name. In our case, it is the atest table we just created. The hacker may need to pad the number of columns to produce an improper response, but it is well possible. Once hackers know something about your schema, they are bound to carry out some malicious activity:

SET QUOTED_IDENTIFIER OFF DECLARE @InputText VARCHAR(100), @command VARCHAR(1000)

SELECT @InputText ="';DROP TABLE ATEST -- "

In this case it's really nasty – they are dropping a table! Remember that, earlier we had mentioned that usually most of the objects in a database are owned by DBO . If you have your objects owned instead by a non-default role (for example, the APP role ), this injection fails to delete the table, as the object owner was not explicitly called.

What really makes this injection attack possible, though, is not that you relied upon the DBO as the implicit owner, but that the proxy account coming from the web application was all too powerful. Here we made use of DBO privileges. Imagine the consequences of mistakenly granting that proxy account sa privileges. All too often, poor coding techniques on the front end of an application which allow SQL injection are backed up by poor DBO security techniques.

Remember, SQL injection is not a SQL Server problem; it is a vulnerability inherent in front-end applications.

The Solutions

Now we've seen the problems, let's tackle the solutions.

Solution 1

For example, let's take VB.NET. In VB.NET, when you build a SQL Statement, instead of building a straight command, use the Parameters (syntactically SqlParameter ) collection to define an @au_id parameter.

For a stored procedure running through VB.NET, explicitly define the command string type, as a CommandType to StoredProcedure . Then, again, use the Parameters collection to define the input parameters for the stored procedure.

When you use the Parameters collection in VB.NET, as in this case, no matter what malicious input the user provides, it is always treated as a literal string. Also, by using the Parameters collection, data type and length checks are more easily enforced.

What happens under the covers, when the Parameters collection is used? VB.NET roles together a fully defined SP_EXECUTESQL command. Imagine that our InputText.Text box was sent to a parameter called @au_id , which was VARCHAR(100) . This is how our SQL sample code would model the change:

SET QUOTED_IDENTIFIER OFF DECLARE @InputText VARCHAR(100), @command VARCHAR(1000)

SELECT @InputText = "527-72-3246" SELECT @InputText = "' ; INSERT INTO jobs (job_desc, min_lvl, max_lvl) VALUES ('Important Job',25,100) -- "

SELECT @InputText AS 'InputText.Text' EXECUTE sp_executesql N'select au_fname,au_lname FROM authors WHERE au_id = @au_id', N'@au_id VARCHAR(100)', @au_id = @InputText

In the above example, we can alternatively comment out the two @InputText variable assignments of the good input and the malicious input. No matter which one fires, you will not have a malicious result because in this syntactical structure InputText.Text is always treated as a literal.

Solution 2

Another method of foiling SQL injection attacks is to filter the user input for SQL characters. Use the REPLACE function to replace any apostrophe (single quotation mark to SQL) with an additional apostrophe. Within a SQL string, two consecutive single quotation marks are treated as an instance of the apostrophe character within the string, rather than as the unexpected delimiters.

In our Query Analyzer test scenario, notice the line with the REPLACE function that we inserted:

SET QUOTED_IDENTIFIER OFF DECLARE @InputText varchar(100), @command varchar(1000) SELECT @InputText = "' ; INSERT INTO jobs (job_desc, min_lvl, max_lvl) VALUES ('Important Job',25,100) --" SELECT @InputText = REPLACE(@InputText,"'",""") SELECT @InputText AS 'InputText.Text' SELECT @command = "select au_fname,au_lname from authors WHERE au_id = '" + @InputText + "'" SELECT @command AS 'Executable SQL Command' EXEC (@command)

There are other escape characters that, in addition to the single quotation mark, needs to be routinely replaced in the front-end code. What should bother us now are the pattern matching characters (for example, single quote included for completeness) that would assume importance in a LIKE statement. The REPLACE function converts a character, as shown:

A Better Solution

We've have looked at two choices for avoiding SQL injection attacks. Of the two, using the Parameters collection is preferred. In the REPLACE function instance, a programmer will code a function that passes our notorious InputText.Text input string to be analyzed for all four of the above replacements . VB is notorious for poorly handling string functions in such a situation. As often as not, by the time you are through, you'll have three copies of the string sitting in memory somewhere on your application, one representing the original string, one in the memory space of the function, and one for the final invocation of the SQL code. For a busy web application, this can be burdensome on machine resources.

On the other hand, when your VB programmer uses the Parameters collection, only one instance of the string will reside in the memory space of the application. Additionally, as we pointed out earlier, using the Parameters collection for ad-hoc SQL statements in our call to SQL causes the use of the SP_EXECUTESQL system extended stored procedure. This procedure is designed to cache query plans where possible, giving improved performance.

There are two more best practices for preventing SQL injection attacks. Remember the injection sample that caused the dropping of our ATEST table? This was possible only because the proxy account of the web application, or COM+ middle layer, had permissions that were too broad. This brings up the next best practice:

In fact, it is worth repeating here another one of our earlier security tips.