__________________________________________________________ The U.S. Department of Energy Computer Incident Advisory Capability ___ __ __ _ ___ / | /_\ / \___ __|__ / \ \___ __________________________________________________________ TECHNICAL BULLETIN Protecting Against SQL Injection Attacks September 6, 2006 22:00 GMT Number CIACTech06-001 [REVISED 28 Apr 2008] ______________________________________________________________________________ PROBLEM: With more and more web servers forming a front end for a database, the risk that an intruder can use SQL injection to compromise that server has increased significantly. Recently we have seen an increase in SQL injection attacks on systems furthered by rootkits designed to easily exploit systems with SQL injection vulnerabilities. PLATFORM: Web servers with a database backend. ABSTRACT: SQL injection is a real threat that is being used to exploit company systems and data. This threat can be reduced by a combination of good programming practice, application firewalls, and scanning. ______________________________________________________________________________ LINKS: CIAC BULLETIN: http://www.ciac.org/ciac/techbull/CIACTech06-001.shtml Microsoft ASP.NET: http://msdn2.microsoft.com/en-us/library/ms998271.aspx ______________________________________________________________________________ REVISION HISTORY: 4/28/2008 - Revised CIACTech06-001 to include Microsoft's technical article regarding SQL injection in ASP.NET web pages. Introduction ============ Recent system compromises have resulted from an exploitation of SQL injection vulnerabilities in the compromised servers. What most people do not realize is that SQL injection attacks can do more than view or change parts of a database. Depending on the SQL server and the options available, they can cause commands to be executed on the server itself. The most common pathway for an SQL injection attack is via a web server backed up with a database. An SQL injection vulnerability results when a user is able to change the SQL queries sent to the database in ways that were unexpected by the designer of the website. For example, the simplest attack involves changing the record number in a query to let the user view or change information in a different record. If that record contains information that that the user is not supposed to be able to see, the web application is exploitable. The most common cause for an SQL injection vulnerability is when the web application developer does not adequately check values received from a web form before passing those values onto the database server. If the values aren’t checked, the user can send any values he wants to the server and get them executed. Operation of a SQL Injection Attack =================================== A Simple Example ---------------- The simplest SQL injection attack results when the user can change the value of a record or user ID to change which record is viewed on the returning form. For example, consider the following piece of a web page that allows a user to query their contact information from the database.
When the button, “Query Database” is pressed, the hidden string named ID is passed to the runquery.cgi program which creates an SQL query, sends it to the database, and then formats and returns the database reply to the user. The value of ID is the record number in the database that contains the user’s data. The value of ID was determined when the user logged into the website and is included as a hidden field on the form. Now, this may seem very straight forward but what would happen if the user were to change the value of ID when he submits the form? If the runquery.cgi program tests to see if the value is an integer but does not check to see if it is the record ID that belongs to the logged in user, the user could view the information of any user he chooses. If, instead of querying a user’s information, the form were for updating the user’s information, changing the value of the record ID could be used to overwrite another user’s information. A More Complex Example ---------------------- While the simple example might be worrisome, it does not allow an intruder to take over a system, just compromise some of the data. The following example shows how SQL injection can be used to run programs on a server. Take the simple example above again, but consider what would happen if the runquery.cgi script were to simply take the value of the ID string and insert it into an SQL query. An SQL query is just a string that contains SQL commands and parameters that are passed to the database server to tell it what to do. The following query takes the ID and returns the user’s name and two address fields. SELECT Users.ID, Users.Name, Users.Address1, Users.Address2 FROM Users WHERE Users.ID=12345; The runquery.cgi program could build a string named Query by concatenating (&) the beginning of the SQL query, the ID returned from the web page and the end of the query, Query = ”SELECT Users.ID, Users.Name, Users.Address1, Users.Address2 FROM Users WHERE Users.ID=” & ID & “;” It would then take the string Query and pass it to the database program. If the runquery.cgi program does not check the ID string to make sure it is an integer and that it is the record number belonging to the logged in user, SQL injection can occur. In a recent incident, the ID string on the web page was changed to something like the following: ID=12345;EXEC%20MASTER..XP_CMDSHELL%20'netstat%20-an%20>%20C:\ TEMP.log'; DROP%20TABLE%20TEMP_TABLE;CREATE%20TABLE%20TEMP_TABLE(ResultTxt%20 varchar(7996)%20NULL);BULK%20INSERT%20[Users]..[TEMP_TABLE]%20FROM%20 'C:\TEMP.log'%20WITH%20(KEEPNULLS);Alter%20Table%20TEMP_TABLE%20add%20ID%20 int%20NOT%20NULL%20IDENTITY%20(1,1) When this string was inserted in place of ID in the SQL query, the semicolon (;) terminated the existing query and then added a bunch of new commands to its right. Separating the new commands so you can read them (%20; = space) yields the following. ID=12345; EXEC MASTER..XP_CMDSHELL 'netstat –an > C:\ TEMP.log'; DROP TABLE TEMP_TABLE; CREATE TABLE TEMP_TABLE(ResultTxt varchar(7996) NULL); BULK INSERT [Users]..[TEMP_TABLE] FROM 'C:\TEMP.log' WITH (KEEPNULLS); Alter Table TEMP_TABLE add ID int NOT NULL IDENTITY (1,1) The SQL (Microsoft SQLServer) command XP_CMDSHELL runs an external command. In this case, it runs netstat –an and pipes the results into c:\temp.log. The next command deletes the table TEMP_TABLE if it exists. It then creates a new TEMP_TABLE that consists of lots of long lines of text and uses the BULK INSERT command to dump the contents of temp.log into the new table. The last command adds a new column to the table named ID that contains sequential integers starting at 1. Following this command are additional SQL injected commands that read out the table, one line at a time so the intruder can see the results of the netstat command. Using this methodology, any command on the system can be run and the results piped back to the intruder. The only error made by the application programmer was to assume that the value of ID that he put in the web page would be the one that was returned when the button was clicked. The result, though, was that the whole system was easily compromised by an intruder. Vulnerabilities that Allow SQL Injection to Occur ================================================= The primary vulnerability that allows SQL injection to occur is that the input received from the web page was not thoroughly tested before being used to create an SQL query. This allowed the intruder to essentially write part of the query. Additional vulnerabilities that exacerbate the problem are the inclusion in a production system of SQL commands that can reach outside of the database and run commands on the system itself and the inclusion of the database server on the same system as the web server. Changing any of these items would have either prevented the attack or at least limited what the intruder could accomplish. Detecting SQL Injection Attacks =============================== Assuming your web page has not been compromised or some other overt symptom of an attack is not letting you know that there is a problem, detecting SQL Injection attacks can done in several different ways. The most common is to read your web server logs. SQL injection attacks create very visible differences in your logs. For example, an attack similar to the one above generates larger than normal entries in the log files. First is a normal log entry. 2006-08-21 04:18:37 xxx.xxx.xxx.xxx GET /onlinecatalog/Scripts/ProductList.asp showdetails=true&idSuper=0&browse=ptype&showprods=true&Type=38&idCategory=69 &idProduct=1859;declare%20@a-- 80 - xxx.xxx.xxx.xxx Mozilla/4.0+(compatible; +MSIE+6.0;+Windows+98;+.NET+CLR+1.1.4322) 200 0 0 This entry has the SQL injection. 2006-08-21 04:21:49 xxx.xxx.xxx.xxx GET /onlinecatalog/Scripts/ProductList.asp showdetails=true&idSuper=0&browse=ptype&showprods=true&Type=38&idCategory=69 &idProduct=1859;CREATE%20TABLE%20[X_5848]([id]%20int%20NOT%20NULL%20IDENTITY %20(1,1),%20[ResultTxt]%20nvarchar(4000)%20NULL);insert%20into%20[X_5848] (ResultTxt)%20exec%20master.dbo.xp_cmdshell%20'Dir%20C:\';insert%20into%20 [X_5848]%20values%20('g_over');exec%20master.dbo.sp_dropextendedproc%20 'xp_cmdshell' 80 - xxx.xxx.xxx.xxx Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98; +.NET+CLR+1.1.4322) 200 0 0 This particular example stuffs a directory listing into a table where it can then be displayed by reading back the table one record at a time. Other ways of detecting attacks are 404 and other error entries in the web logs and error logs generated by your scripts that are checking the input and creating the SQL commands. Protecting Against SQL Injection Attacks ======================================== So how do you protect against SQL injection attacks? The first step is to recognize that they do occur and that they are caused by relatively simple programming errors. Next there are several steps you can take to prevent SQL injection attacks. The best protection is to use good programming practice to make it impossible to inject other code into the SQL commands. After that, you can do things like separating the web and SQL servers, adding application firewalls, and scanning for application server vulnerabilities. Good programming Practice ------------------------- By far, the best way to protect against SQL injection attacks is to follow good programming practice. The primary practice here is to always check all input data before using it. Even though that data should come from a web page you created, it is actually coming to you from some user’s web browser where it could be changed. Especially do not take a string returned by a web page and insert it, unchecked, directly into an SQL statement as that is just asking to be exploited. Check that the returned strings are of the correct data type and also check for any extra characters, especially the semicolon (;) which is an SQL command terminator and the single quote (‘) which is an SQL escape. Keep in mind that %3b also evaluates to a semicolon in an html request. When checking a string for unwanted characters, you must search for all possible variants of that character, including those encoded with %. Block and log any web queries that do not pass your tests. A second good programming practice is to remove server capabilities that you do not need in your production servers. Especially consider capabilities like those that allow the SQL server to run shell commands or any commands outside of the database. The Microsoft tool IISLockdown is available for free to remove capabilities of an IIS server that are not needed in a production environment, to improve its security. If the server deals in sensitive information, authenticate the user with every web page request. A simple way to do this is to use transient cookies. Your server gives a cookie to the client when the user logs in and the client returns that cookie with every request. Usernames, database record numbers and other information can be maintained on the server and tied to the cookie so the user never sees them and cannot change them. While a user could change a cookie, he would have to pick the cookie ID of someone who is currently logged onto the server in order to get at that person’s data. As long as cookie IDs are assigned randomly and are invalidated when the user logs off, picking a cookie that matches another person’s cookie is a very low probability event. You should suppress sending detailed error messages from the database server via the web server to the user on a production system as this tells a malicious user too much about your system. Use a general error message instead. When setting up the system, the web server is given an account name to login to the database server. If there is no reason for the logged in user to change the database, make the database account a read-only account so changes cannot be made. Restrict the database account as much as possible in a production system. Separate the Web Server and the SQL Server ------------------------------------------ If at all possible, the web server and the SQL server should be on different machines. By separating the machines, any improper SQL commands will run on the SQL server and not on the web server where the intruder has access. If you also block Internet access to the SQL server any backdoor started on the server by the SQL injection will not be able to go anywhere. The best configuration is to put the SQL server on a separate machine on a backnet. That is, your web server has two NICs, one connected to the Internet and the other connected to a small network containing your SQL server and that is not connected to the Internet. Do not allow your web server to route packets from the Internet NIC to the backnet NIC and use non-routable addresses in the backnet or use a different protocol such as Novell instead of TCP/IP. If the SQL server must be on the same network as the web server, block access to it from the Internet using a firewall or use a non routing IP address (such as 192.168.x.x). You will need to add a route to the web server so it will be able to find the database server but packets from the database server will not be able to go beyond the first router. Application Server Firewalls ---------------------------- A new class of firewalls is available that run at the application level and check all input for adherence to a set of rules. These rules are compared against the incoming web requests and block any that do not fit the pattern of acceptable input for your server. Application firewalls are available as both hardware platforms such (for example, NetContinuum, TrafficShield) that sit in front of your web server and as software (for example, SecureIIS, InterDo, AppShield, mod_security) that runs on the webserver itself. The Microsoft tool, URLScan, is available free for use with Microsoft servers and blocks many types of inappropriate behavior. Scanning for Application Server Vulnerabilities ----------------------------------------------- A new class of scanners (such as Web Inspect) is available for locating SQL injection and a lot of other vulnerabilities related to web servers. These scanners are not simple scan and forget applications but must be actively operated by both the scanner and the application developer. Some helpful tools are available like the Web Developer toolbar to help a developer know what values he is exposing in a web page. http://chrispederick.com/work/webdeveloper/ This toolbar allows you to look at your web page, view any fields exposed in a form and change any of those fields to see what will happen. ASP.NET Applications ==================== In light of the recent wave of SQL injection attacks targeting ASP.NET web servers, Microsoft has published a technical article outlining how to properly create and enforce input sanitization, utilize safe data access procedures, and provide safe database access from within ASP.NET web pages. The Microsoft technical article can be found here: http://msdn2.microsoft.com/en-us/library/ms998271.aspx Conclusion ========== The combination of a web server and a database has created an important information sharing and gathering product. Anything that can be placed in a database; product data, user contacts, documents lists and others, can have a web front end and be accessible via the Internet. Unfortunately, this usefulness can be exploited by simple application programming errors that allow an intruder to inject SQL commands into the database server and access your database or possibly your whole system in ways you did not intend. Proper programming practice, application firewalls, and scanning can reduce the occurrence of SQL injection vulnerabilities and protect your systems and data. ______________________________________________________________________________ CIAC wishes to acknowledge the contributions of Joshua Knust and Microsoft for some of the information contained in this bulletin. ______________________________________________________________________________ CIAC, the Computer Incident Advisory Capability, is the computer security incident response team for the U.S. Department of Energy (DOE) and the emergency backup response team for the National Institutes of Health (NIH). CIAC is located at the Lawrence Livermore National Laboratory in Livermore, California. CIAC is also a founding member of FIRST, the Forum of Incident Response and Security Teams, a global organization established to foster cooperation and coordination among computer security teams worldwide. CIAC services are available to DOE, DOE contractors, and the NIH. CIAC can be contacted at: Voice: +1 925-422-8193 (7x24) FAX: +1 925-423-8002 STU-III: +1 925-423-2604 E-mail: ciac@ciac.org Previous CIAC notices, anti-virus software, and other information are available from the CIAC Computer Security Archive. World Wide Web: http://www.ciac.org/ Anonymous FTP: ftp.ciac.org PLEASE NOTE: Many users outside of the DOE, ESnet, and NIH computing communities receive CIAC bulletins. If you are not part of these communities, please contact your agency's response team to report incidents. Your agency's team will coordinate with CIAC. The Forum of Incident Response and Security Teams (FIRST) is a world-wide organization. A list of FIRST member organizations and their constituencies can be obtained via WWW at http://www.first.org/. This document was prepared as an account of work sponsored by an agency of the United States Government. Neither the United States Government nor the University of California nor any of their employees, makes any warranty, express or implied, or assumes any legal liability or responsibility for the accuracy, completeness, or usefulness of any information, apparatus, product, or process disclosed, or represents that its use would not infringe privately owned rights. Reference herein to any specific commercial products, process, or service by trade name, trademark, manufacturer, or otherwise, does not necessarily constitute or imply its endorsement, recommendation or favoring by the United States Government or the University of California. The views and opinions of authors expressed herein do not necessarily state or reflect those of the United States Government or the University of California, and shall not be used for advertising or product endorsement purposes. LAST 10 CIAC BULLETINS ISSUED (Previous bulletins available from CIAC) Q-293: Kernel Security Update Q-294: Multiple Security Vulnerabilities in Mozilla 1.4 and 1.7 Q-295: ImageMagick Security Update Q-296: HP OpenView Storage Data Protector Q-297: Cisco Unintentional Password Modification Vulnerability in Cisco Firewall Products Q-298: Cisco VPN 3000 Concentrator FTP Management Vulnerabilities Q-299: VMware ESX Server 2.5.3 Upgrade Patch 2 Q-300: Security Vulnerability in the Sun Java System Content Delivery Server Q-301: pkgadd(1M) May Set Incorrect Permissions Q-302: mysql-dfsg-4.1