Security

Ιστογνώσις ΛΤΔ Σχεδίαση και ανάπτυξη ιστοσελίδων και εφαρμογών διαδικτύου

The phrase "security vulnerability" gives most of us the creeps. We are afraid of them. Looking at them rationally, they share a trait with other bugs: All are a pain, but some are a bigger pain than others. And instead of trying to avoid the evil by not talking about it, let's take a look at MySQL's security vulnerability track record over the years, and explain the rationale behind our actions using actual cases from past years.

Let me start by pointing out that the MySQL database -- like all large software products -- has had, and in all likelihood still has, security vulnerabilities. As an often used default database for web apps, MySQL is pounded upon from a number of different directions. Since one of the most important user criteria is that the database must be secure, let me also be clear that MySQL treats security vulnerabilities as the highest-priority of all bugs and promptly allocates resources towards fixing such bugs when they are reported, as described below.

Yet, every single alleged security vulnerability does not merit MySQL's release of new binaries, nor do all of them require you as a user to upgrade your version of the database.

So in order to better be able to assess any future threat situation, let us take a deep breath and explore what "security vulnerabilities" are, how they are classified, how they are discovered, how MySQL gets to know about them, what the MySQL Security Team is and what the roles of the team members are, how the vulnerabilities are fixed, how the fixes are distributed and communicated, and whether you should care.

Security Vulnerability: The definition

Looking up "security vulnerability" in Wikipedia provides the following definition (http://en.wikipedia.org/wiki/Security_vulnerability):

"In computer security, the word vulnerability refers to a weakness in a system allowing an attacker to violate the confidentiality, integrity, availability, access control, consistency or audit mechanisms of the system or the data and applications it hosts. Vulnerabilities may result from bugs or design flaws in the system. A vulnerability can exist either only in theory, or could have a known exploit. Vulnerabilities are of significant interest when the program containing the vulnerability operates with special privileges, performs authentication or provides easy access to user data or facilities (such as a network server or RDBMS)."

The Wikipedia article points out the possible causes of vulnerabilities:

"Vulnerabilities often result from the carelessness of a programmer, though they may have other causes. A vulnerability may allow an attacker to misuse an application through (for example) bypassing access control checks or executing commands on the system hosting the application. Some vulnerabilities arise from un-sanitized user input, often allowing the direct execution of commands or SQL statements (known as SQL injection). Others arise from the programmer's failure to check the size of data buffers, which can then be overflowed, causing corruption of the stack or heap areas of memory (including causing the computer to execute code provided by the attacker)."

The MySQL Security Team

The MySQL Security Team is not a dedicated, separate team but a cross-functional group of employees who in case of a verified security vulnerability are prepared to promptly focus on the issue until the problem is fixed and applied to the source code. Depending on the nature of the security issue, a build is made, and the issue isn't closed until affected customers have been alerted and the vulnerability, and the subsequent 'fix' has been communicated to the user base. For more on how MySQL prioritizes security vulnerabilities and our response guidelines, see the section entitled “Rationale: Not all security vulnerabilities are created equal” below.

The security team can be contacted via Αυτή η διεύθυνση ηλεκτρονικού ταχυδρομείου προστατεύεται από κακόβουλη χρήση. Χρειάζεται να ενεργοποιήσετε την Javascript για να τη δείτε. . The security team is comprised of:

1. several server developers, whose task it is to verify the bug report and fix the bug
2. several representatives from the Build (Engineering) team, whose task it is to build a new release with the fix
3. several support engineers, whose task it is to alert individual customers and who also verify bug reports
4. several representatives from the Marketing and Community teams, whose task it is to communicate information to our user base
5. several management representatives, whose task it is to ensure that relevant resources are allocated

The security team convenes not only when a severe security vulnerability is reported, but also regularly resolves less urgent security vulnerabilities.

What happens when a security vulnerability occurs?

If you discover what you think is a security vulnerability, please send a bug report to Αυτή η διεύθυνση ηλεκτρονικού ταχυδρομείου προστατεύεται από κακόβουλη χρήση. Χρειάζεται να ενεργοποιήσετε την Javascript για να τη δείτε. . You are then the "

Please don't report security vulnerabilities as a bug through bugs.mysql.com. The rationale behind this is to decrease the likelihood of exploitation of the security vulnerability during the time we are fixing the vulnerability as bugs.mysql.com can be viewed publicly. Our best security vulnerability reporters (those that write the largest number reports, as well as those who make the most complete, comprehensive and easy-to-reproduce vulnerability reports) have been very cooperative in reporting to Αυτή η διεύθυνση ηλεκτρονικού ταχυδρομείου προστατεύεται από κακόβουλη χρήση. Χρειάζεται να ενεργοποιήσετε την Javascript για να τη δείτε. , and not issued publicly accessible reports until we've been given a fair chance to fix the issue.

Those are the steps external to MySQL. What happens once MySQL receives a vulnerability report?

First, if the security vulnerability is sent in through bugs.mysql.com, we may mark the bug private, in effect making it invisible outside the members of the Security Team plus a few other developers at MySQL AB.

Second, we allocate resources based on the anticipated effects of the security vulnerability. (For our rationale when assessing the bug, read on.)

Third, we verify the bug, using our normal bug verification processes (which may involve the Security Team members) but with a priority that surpasses all non-security issues.

Fourth, if an alleged security vulnerability is verified, our bug fixing resources are immediately reprioritised to fix the security bug before all other ongoing work. In severe cases, this may mean calling people out of their beds.

Fifth, the fix is reviewed by other developers and documented by a Documentation Team member, adding a note to the Release Notes. Again, these reviews are prioritised over outstanding tasks of the needed resources, and for severe cases, the additional reviewers have already been notified in step two and are standing-by to assist.

Sixth, for vulnerabilities where non-MySQLers haven't applied for a CVE number from MITRE (for a definition, see below), we will do so. However, we haven't done so in all cases during the last nine years of MySQL, and we're improving our practices in this area.

Seventh and last, the MySQL Build Team creates the official release, publishes and announces it in cooperation with our Community team. For our commercial customers on MySQL Enterprise, a separate update alert is written and broadcast.

Rationale: Not all security vulnerabilities are created equal

The rationale for our actions and priorities behind fixing security vulnerabilities is based on common sense reasoning. MySQL does not have a formal Security Policy, by which an outside authority could determine which treatment we give a particular security vulnerability. At some point, we may choose to publish an explicit Security Policy and your input regarding a Security Plan is welcome. Feel free to send your ideas to the security mailing list, to the equivalent Community Team mailing list ( Αυτή η διεύθυνση ηλεκτρονικού ταχυδρομείου προστατεύεται από κακόβουλη χρήση. Χρειάζεται να ενεργοποιήσετε την Javascript για να τη δείτε. ), or to Lenz and myself at Αυτή η διεύθυνση ηλεκτρονικού ταχυδρομείου προστατεύεται από κακόβουλη χρήση. Χρειάζεται να ενεργοποιήσετε την Javascript για να τη δείτε. .

Criterion 1: Scope extends outside MySQL?

The most important factor that determines the seriousness of a security vulnerability is whether its effects are limited to the realm of MySQL, or if it affects the whole operating system, outside the realm of MySQL. This may occur for certain types of bugs, such as buffer overflows, where in some situations the exploiter can execute random machine code pushed by the exploiter.

Realm of vulnerability: MySQL or arbitrary code execution

• Bad: MySQL Server is vulnerable
• Worse: The entire Operating System is made vulnerable, through MySQL enabling arbitrary code execution

Criterion 2: Remote or local user?

The second most important factor that determines the seriousness of a security vulnerability is whether it occurs through a remote application user, without operating system privileges, or whether the MySQL Server is vulnerable only by local attack by users carrying credentials on the Operating System level.

Required OS privileges for triggering the vulnerability

• Bad: A named account with shell access to the OS can cause MySQL to crash (or gain access to data)
• Worse: Any unnamed remote user of the OS, even without privileges/credentials for the operating system, can cause MySQL to crash (or gain access to data)

Criterion 3: MySQL-level authentication

A third factor that determines the seriousness of a security vulnerability is whether it requires MySQL privileges or not.

Required MySQL privileges for triggering the vulnerability

• Bad: A named MySQL user account can cause MySQL to crash (or gain access to data) beyond the rights of that user
• Worse: Any unnamed remote user of a MySQL server, even without MySQL privileges, can cause MySQL to crash (or gain access to data)

Criterion 4: Likelihood of an exploit

The fourth factor, the likelihood of an exploit, is hard to quantify. At one extreme end, we have exploits which are theoretical; there are no known exploits. At the other end, we have exploits with a how-to-reconstruct published on the web. Between these far ends, there is a continuum where only experience can be used to judge the real likelihood.

Likelihood of an exploit

• Bad: The risk of a vulnerability being potentially exploitable
• Worse: Exploits that are not just known, but published

Criterion 5: Gaining access or crashing the server

While "crashing the server" may look serious, because of the connotation of the word "crash", arguably it's much worse if access to real data is gained.

Nature of vulnerability: Server crash or access to data

• Bad: MySQL Server crashes -- the execution of the mysqld process stops
• Worse: Unprivileged users get access to data -- reading or writing to the database without proper rights. This is worse for two reasons: (1) because sensitive data can be leaked or altered, and (2) because it won't be detected as promptly as a crash.

Further criteria

There are further criteria, some of which may explain some of our reasoning towards the likelihood of an exploit.

Type of MySQL software which is vulnerable

• Bad: Any vulnerable software, including scripts and clients
• Worse: The Server itself, where the vulnerability is usually not trivially fixed by an improved configuration

Denial of Service -- or potentially corrupting data

• Bad: Ill-willed users of a MySQL-based web site can slow down the Server so much that it becomes practically unusable
• Worse: They not only cause the Server to slow down -- they crash the Server, which requires the DBA to restart the Server and can leave some tables in a corrupted state

Effect of security precautions

• Bad: Any security bug, even if it just occurs when MySQL is running as Unix root on the server, or the "test" database and empty userid are left in a production environment
• Worse: A bug that makes MySQL vulnerable despite the DBA having followed best practices for securing a MySQL installation

Maturity of version on which the bug occurs

• Bad: Any security bug, even if it's on an alpha version
• Worse: A bug on a GA version, during that version's prime lifetime

What is an "Exploitable" Security Vulnerability?

Today, MySQL has no formal definition of an "exploitable" security vulnerability. We try to err on the side of being cautious, but on the other hand, we do want to react more swiftly to real threats than potential ones. Hence, for the purpose of this article, I've used the expression "exploitable" to mean "a known exploit exists, or is likely to exist within a short timeframe", as opposed to "theoretically possible to exploit within a finite timeframe". Both definitions are defensible, and we've picked ours with the interests of the users in mind. If you have improvement suggestions, tell us!

Summary: Policy on MySQL Security Vulnerabilities

While MySQL does not have a formal Security Policy, in practice, we intend to give vulnerabilities roughly three levels of treatment:

Severity A:

Attempted time to fix: Within hours of discovery. New MySQL Enterprise Server binaries issued: Within a week (ASAP, often quicker). New MySQL Community Server binaries issued: Within two weeks (ASAP, often quicker). Triggering effects:

• exploitable, arbitrary code execution
• exploitable, unauthenticated user gains access or crashes the server

Severity B:

Attempted time to fix: Within hours of discovery. New MySQL Enterprise Server binaries issued: Within two weeks (ASAP, often quicker). New MySQL Community Server binaries issued: No. Triggering effects:

• not exploitable arbitrary code execution

Severity C:

Attempted time to fix: Within days or weeks of discovery. New MySQL Enterprise Server binaries issued: No. New MySQL Community Server binaries issued: No. Triggering effects:

• any other security vulnerability

MITRE tracks vulnerabilities independently

Quite like most societies have set up independent policing authorities and don't rely on private security companies, the concept of an independent security organisation exists in the IT industry. The most authoritative of these is probably the MITRE Corporation (http://www.mitre.org), a US-based not-for-profit organization chartered to work in the public interest.

MITRE keeps a list of CVEs or Common Vulnerabilities and Exposures. Some of these are related to MySQL. When I searched the MITRE web site with the keyword "mysql" (http://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=mysql) in late June 2007, I got 137 CVE entries, with the first one from 1999. Most of these entries ended up on the list because they are related to applications that use MySQL, so the more relevant number is the number of entries related to MySQL software provided by MySQL AB itself (notably MySQL Server, but in some cases also the Client software). Applying this criterion gives us 58 entries, of which two were disputed by MySQL AB at the time they occurred.

I asked MySQL's co-founder and MySQL Fellow, Michael "Monty" Widenius to take a look at these 56 security vulnerabilities, reported over a total of 9 years, and classify them. Based on this work, Lenz Grimmer (now in our Community Relations Team, but with a past track record in the Build Team) and Giuseppe Maxia (now in our Community Relations Team, but with a past track record as an external MySQL community member and a member of the MySQL Quality Assurance Team) documented a list of all individual cases. This list is now available on MySQL Forge at http://forge.mysql.com/wiki/Security_Vulnerabilities_In_MySQL_Server, with CVE numbers, dates and times when they were fixed in MySQL. I got plenty of help from several members of the Security Team at MySQL.

Historic MySQL CVE entries 1999-mid 2007

To learn more from MySQL's past track record, and to ensure a good security policy in the future, we went through all 56 Security Vulnerabilities from 1999 to mid-2007 and placed them into seven categories (worst case first):

1. MySQL users can execute arbitrary (non-MySQL related) code of their own choice on the server
2. Remote users can gain access to the MySQL Server
3. Remote users can crash the MySQL Server
4. Local users can gain access to the MySQL Server
5. Local users can crash the MySQL Server
6. Minor Security Vulnerability (e.g. configuration issue)
7. Not a real Security Vulnerability (MySQL works as defined)

Definitions: A "remote user" is a user of MySQL, regardless of point of access, who does not have shell access to the underlying operating system that MySQL runs on. A "local user", on top of MySQL access, also has shell access.

Of these, the first six are true, undisputable vulnerabilities. The seventh one depends on the viewpoint. We may see it as "not a real problem", whereas one may also claim that this last category consists of design flaws. There are three such CVE entries from 1999 to mid-2007.

Year	Hijack	Remote user		Local user		Minor	None	Total
	server	access	crash	access	crash
	1	2	3	4	5	6	7
1999			1					1
2000		1	2					3
2001	1		1	3				5
2002		1	1		1	4	1	8
2003				1	2			3
2004					2	8	1	11
2005				5	1	3		9
2006		1		3	3	3	1	11
2007				1	2	2		5
Total	1	3	5	13	11	20	3	56

Source: Security Vulnerabilities in the MySQL Server by Lenz Grimmer, classified by Michael "Monty" Widenius

Let me provide you with all known examples of security vulnerabilities giving remote users access to MySQL Server:

1. CVE-2000-0148
   MySQL 3.22 allows remote attackers to bypass password authentication and access a database via a short check string. Reported 8 Feb 2000, fixed in 3.22.32 on 14 February 2000.
2. CVE-2002-1374
   The COM_CHANGE_USER command in MySQL 3.x before 3.23.54, and 4.x before 4.0.6, allows remote attackers to gain privileges via a brute force attack using a one-character password, which causes MySQL to only compare the provided password against the first character of the real password. Reported 3 Dec 2002, fixed in 3.23.54 on 5 Dec 2002 and in 4.0.6 on 14 Dec 2002.
3. CVE-2006-2753
   SQL injection vulnerability in MySQL 4.1.x before 4.1.20 and 5.0.x before 5.0.22 allows context-dependent attackers to execute arbitrary SQL commands via crafted multibyte encodings in character sets such as SJIS, BIG5, and GBK, which are not properly handled when the mysql_real_escape function is used to escape the input. Originally reported as bug 8378 on 7 Feb 2005, then again as a security bug in May 2006, fixed in 4.1.20 on 24 May 2006 and in 5.0.22 on 24 May 2006.

Some sample security vulnerabilities through which remote users can crash MySQL Server:

1. CVE-1999-1188
   mysqld in MySQL 3.21 creates log files with world-readable permissions, which allows local users to obtain passwords for users who are added to the user database. Reported on 26-Dec-1999. Fixed in 3.22.x
2. CVE-2000-0045
   MySQL allows local users to modify passwords for arbitrary MySQL users via the GRANT privilege. Reported on 11-Jan-2000. Fixed the same day, in 3.22.30.
3. CVE-2000-0981
   MySQL Database Engine uses a weak authentication method which leaks information that could be used by a remote attacker to recover the password. Reported on 8-Feb-2000. Fixed in 4.1.0 (4-Apr-2003)
4. CVE-2001-1454
   Buffer overflow in MySQL before 3.23.33 allows remote attackers to execute arbitrary code via a long drop database request. Reported on 26-Jan-2001. Fixed in 3.23.33 (9-Feb-2001)
5. CVE-2002-1375
   The COM_CHANGE_USER command in MySQL 3.x before 3.23.54, and 4.x to 4.0.6, allows remote attackers to execute arbitrary code via a long response. Reported on 3-Dec-2002. Fixed in 3.23.54 (5-Dec-2002) and 4.0.6 (14-Dec-2002).

Examples of security vulnerabilities not belonging to the most serious category:

1. CVE-2007-2693
   MySQL before 5.1.18 allows remote authenticated users without SELECT privileges to obtain sensitive information from partitioned tables via an ALTER TABLE statement. Reported on 26-Oct-2006. Fixed in 5.1.18 (8-May-2007)
2. CVE-2007-2691
   MySQL before 4.1.23, 5.0.x before 5.0.42, and 5.1.x before 5.1.18 does not require the DROP privilege for RENAME TABLE statements, which allows remote authenticated users to rename arbitrary tables.
   This was not critical, as it required ALTER on the original table. Reported on 29-Mar-2007. Fixed in 4.1.23 (12-Jun-2007) / 5.0.42 Enterprise (23-May-2007) / 5.0.45 Community (4-Jul-2007) / 5.1.18 (08-May-2007)
3. CVE-2006-1517
   sql_parse.cc in MySQL 4.0.x up to 4.0.26, 4.1.x up to 4.1.18, and 5.0.x up to 5.0.20 allows remote attackers to obtain sensitive information via a COM_TABLE_DUMP request with an incorrect packet length, which includes portions of memory in an error message. Reported on 25-Apr-2006. Fixed in 4.0.27 (6-May-2006), 4.1.19 (6-May-2006), and 5.0.22 (2-May-2006).
4. CVE-2006-1516
   The check_connection function in sql_parse.cc in MySQL 4.0.x up to 4.0.26, 4.1.x up to 4.1.18, and 5.0.x up to 5.0.20 allows remote attackers to read portions of memory via a username without a trailing null byte, which causes a buffer over-read.
   No exploit known. Reported on 25-Apr-2006. Fixed in 4.0.27 (6-May-2006), 4.1.19 (6-May-2006), and 5.0.22 (2-May-2006).
5. CVE-2006-0903
   MySQL 5.0.18 and earlier allows local users to bypass logging mechanisms via SQL queries that contain the NULL character, which are not properly handled by the mysql_real_query function. NOTE: this issue was originally reported for the mysql_query function, but the vendor states that since mysql_query expects a null character, this is not an issue for mysql_query.
   Minor issue, not a problem with binary logging. Reported on 25-Feb-2006. Fixed in 5.0.24 (27-Jul-2006).

Lastly, an example of a vulnerability raised because it represents surprising (to the reporter) behaviour potentially related to security. But not all of these are issues where the unexpected behaviour is unintended. Here is an example from 2006:

1. CVE-2006-4031
   MySQL 4.1 before 4.1.21 and 5.0 before 5.0.24 allows a local user to access a table through a previously created MERGE table, even after the user's privileges are revoked for the original table, which might violate intended security policy.
   This is how a MERGE table is defined to work

To conclude, let's see an example of a false alarm, presented to the MySQL Users Conference 2007. The fear started with an intimidating blog entry (Are you prepared for the attack of the MySQL worm?, followed by bug report #28089). The reporter describes an attack through a chain of compromised MySQL servers via Federated tables as highly probable. Fortunately, at the Users Conference there were all the experts on Federated tables and the report was quickly analyzed. Within hours, the alleged vulnerability was reduced to a largely improbable case, where most DBAs must drop all the default defenses and use lousy passwords in order for the attack to start. This case did not even become a CVE entry. It was defused with the full understanding of everyone involved, including the author of the initial report.

Let us know how we should improve

The purpose of this article is to describe how MySQL has treated security vulnerabilities. It's a documentation of the guidelines according to which we've worked. In case you are involved with security issues, do let us know how we can improve, from the perspective of the MySQL user base in general, or from your specific perspective! You can reach the relevant people under Αυτή η διεύθυνση ηλεκτρονικού ταχυδρομείου προστατεύεται από κακόβουλη χρήση. Χρειάζεται να ενεργοποιήσετε την Javascript για να τη δείτε. , Αυτή η διεύθυνση ηλεκτρονικού ταχυδρομείου προστατεύεται από κακόβουλη χρήση. Χρειάζεται να ενεργοποιήσετε την Javascript για να τη δείτε. or Lenz Grimmer and myself directly under Αυτή η διεύθυνση ηλεκτρονικού ταχυδρομείου προστατεύεται από κακόβουλη χρήση. Χρειάζεται να ενεργοποιήσετε την Javascript για να τη δείτε. .

Kaj Arnö

References

• http://www.microsoft.com/technet/archive/community/columns/security/essays/vulnrbl.mspx?mfr=true Microsoft's Definition of a Security Vulnerability
• http://en.wikipedia.org/wiki/Vulnerability_%28computer_science%29 Vulnerability Definition in Wikipedia
• http://en.wikipedia.org/wiki/Exploit_%28computer_science%29 Definition of an Exploit on Wikipedia
• http://cve.mitre.org/about/definition.html Vulnerability Definition from the CVE Editorial Board
• http://cve.mitre.org/about/terminology.html CVE Terminology: Vulnerability
• http://cve.mitre.org/docs/vuln-trends/index.html Report: Vulnerability Type Distributions in CVE
• http://en.wikipedia.org/wiki/Buffer_overflow Buffer overflow in Wikipedia
• http://en.wikipedia.org/wiki/Denial-of-service_attack Denial of Service in Wikipedia
• http://en.wikipedia.org/wiki/Zero_day Zero-day vulnerability in Wikipedia

Ιστογνώσις ΛΤΔ Σχεδίαση και ανάπτυξη ιστοσελίδων και εφαρμογών διαδικτύου

Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.

In discussing security, we emphasize the necessity of fully protecting the entire server host (not just the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.

MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can attempt to perform. There is also support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications.

When running MySQL, follow these guidelines whenever possible:

Ιστογνώσις ΛΤΔ Σχεδίαση και ανάπτυξη ιστοσελίδων και εφαρμογών διαδικτύου

Ιστογνώσις ΛΤΔ Σχεδίαση και ανάπτυξη ιστοσελίδων και εφαρμογών διαδικτύου

Introduction

Security in software applications is an ever more important topic. In this article, I discuss various aspects of SQL Injection attacks, what to look for in your code, and how to secure it against SQL Injection attacks. Although the technologies used here are SQL Server 2000 and the .NET Framework, the general ideas presented apply to any modern data driven application framework, which makes attacks potentially possible on any type of application that depends on that framework.

What is a SQL Injection Attack?

A SQL Injection attack is a form of attack that comes from user input that has not been checked to see that it is valid. The objective is to fool the database system into running malicious code that will reveal sensitive information or otherwise compromise the server.

There are two main types of attacks. First-order attacks are when the attacker receives the desired result immediately, either by direct response from the application they are interacting with or some other response mechanism, such as email. Second-order attacks are when the attacker injects some data that will reside in the database, but the payload will not be immediately activated. I will discuss each in more detail later in this article.

An example of what an attacker might do

In the following example, assume that a web site is being used to mount an attack on the database. If you think about a typical SQL statement, you might think of something like:

Collapse

SELECT ProductName, QuantityPerUnit, UnitPrice 
FROM Products 
WHERE ProductName LIKE 'G%'

The objective of the attacker is to inject their own SQL into the statement that the application will use to query the database. If, for instance, the above query was generated from a search feature on a web site, then they user may have inserted the "G" as their query. If the server side code then inserts the user input directly into the SQL statement, it might look like this:

Collapse

string sql = "SELECT ProductName, QuantityPerUnit, UnitPrice "+
    "FROM Products " +
    "WHERE ProductName LIKE '"+this.search.Text+"%';
SqlDataAdapter da = new SqlDataAdapter(sql, DbCommand);
da.Fill(productDataSet);

This is all fine if the data is valid, but what if the user types something unexpected? What happens if the user types:

Collapse

' UNION SELECT name, type, id FROM sysobjects;--

Note the initial apostrophe; it closes the opening quote in the original SQL statement. Also, note the two dashes at the end; that starts a comment, which means that anything left in the original SQL statement is ignored.

Now, when the attacker views the page that was meant to list the products the user has searched for, they get a list of all the names of all the objects in the database and the type of object that they are. From this list, the attacker can see that there is a table called Users. If they take note of the id for the Users table, they could then inject the following:

Collapse

' UNION SELECT name, '', length FROM syscolumns 
WHERE id = 1845581613;--

This would give them a list of the column names in the Users table. Now they have enough information to get access to a list of users, passwords, and if they have admin privileges on the web site.

Collapse

' UNION SELECT UserName, Password, IsAdmin FROM Users;--

Assume that there is a table called Users which has columns called UserName and Password, it is possible to union that with the original query and the results will be interpreted as if the UserName was the name of the product and the Password was the quantity per unit. Finally, because the attacker discovered that there is a IsAdmin column, they are likely to retrieve the information in that too.

Locking down

Security is something that needs to be tackled on many levels because a chain is only as strong as its weakest link. When a user interacts with a piece of software, there are many links in the chain; if the user is malicious, he could attempt to attack these links to find the weak point and attempt to break the system at that point. With this in mind, it is important that the developer does not become complacent about the security of the system because one security measure is put in place, or a set of security measures are in place on only one part of the system.

An intranet website that uses Windows authentication (it takes the user's existing credentials based on who they are logged in as) and is sitting inside the corporate network and unavailable to Internet users may give the impression that only authorised users can access the intranet web application. However, it is possible for an authenticated user to gain unauthorised access if the security is not taken much beyond that level. Some statistics support the suggestion that most security breaches are insider jobs rather than people attacking the system from outside.

With this in mind, it is important that even if the application permits only valid data through that has been carefully verified and cleaned up, other security measures are put in place. This is especially important between application layers where there may be an increased opportunity for spoofing of requests or results.

For example, if a web application were to request that the user choose a date, then it would be normal that the values for the date are checked in some JavaScript function on the web page before any data was posted back to the server. This improves the user experience by reducing the wait between lots of server requests. However, the value needs to be validated again on the server as it is possible to spoof the request with a deliberately crafted invalid date.

Encrypting data

Starting from the proposition that somehow an attacker has managed to break through all other defenses, what information is so sensitive that it needs to remain a secret? Candidates for encryption include user log in details or financial information such as credit card details.

For items such as passwords, the user's password can be stored as a "salted hash". What happens is that when a user creates a password, a randomly generated "salt" value is created by the application and appended to the password, and the password-and-salt are then passed through a one way encryption routine, such as found in the .NET Framework's helper class FormsAuthentication (HashPasswordForStoringInConfigFile method). The result is a salted hash which is stored in the database along with the clear text salt string.

The value of a salted hash is such that a dictionary attack is not going to work as each dictionary would have to be rebuilt appending the various salt values and recomputing the hash values for each item. While it is still possible to determine the password by brute force, the use of the salt (even though it is known) greatly slows down the process. The second advantage of the salt is that it masks any situations where two independent users happen to use the same password, as the salted hash value for each user would be different if given different salt values.

Least Privilege - Database account

Running an application that connects to the database using the database's administrator account has the potential for an attacker to perform almost limitless commands with the database. Anything an administrator can do, so can an attacker.

Using the example application above, an attacker could inject the following to discover the contents of the hard disk(s) on the server.

The first command is used to create a temporary store on the database and fill it with some data. The following injected code will create a table with the same structure as the result set of the extended stored procedure that will be called. It then populates the table with the results of the extended stored procedure.

Collapse

'; CREATE TABLE haxor(name varchar(255), mb_free int); 
INSERT INTO haxor  EXEC master..xp_fixeddrives;--

A second injection attack has to take place in order to get the data out again.

Collapse

' UNION SELECT name, cast((mb_free) as varchar(10)), 1.0 FROM haxor;--

This returns the name of the disks with the available capacity in megabytes. Now that the drive letters of the disks are known, a new injection attack can take place in order to find out what is on those disks.

Collapse

'; DROP TABLE haxor;CREATE TABLE haxor(line varchar(255) null); 
INSERT INTO haxor EXEC master..xp_cmdshell 'dir /s c:\';--

And again, a second injection attack is used to get the data out again.

Collapse

' UNION SELECT line, '', 1.0 FROM haxor;-- 

xp_cmdshell, by default, is only executable by a user with the sysadmin privilege, such as sa, and CREATE TABLE is only available to sysadmin,db_dbowner or db_dlladmin users. It is therefore important to run the application with the least privileges that are necessary in order to perform the necessary functions of the application.

Least Privilege - Process account

When an instance of SQL Server is installed on a computer, it creates a service that runs in the background and processes the commands from applications that are connected to it. By default, this service is installed to use the Local System account. This is the most powerful account on a Windows machine, it is even more powerful than the Administrator account.

If an attacker has an opportunity to break out of the confines of SQL Server itself, such as through the extended procedure xp_cmdshell, then they could gain unrestricted access to the machine that the SQL Server is on.

Microsoft recommends that during the installation of SQL Server, the service is given a domain account which has the permissions set to only the necessary resources. That way, an attacker is confined by the permission set needed to run SQL Server.

Cleaning and Validating input

In many applications, the developer has side-stepped the potential use of the apostrophe as a way to get access to the system by performing a string replace on the input given by the user. This is useful for valid reasons, such as being able to enter surnames such as "O'Brian" or "D'Arcy", and so the developer may not even realise that they have partly defeated a SQL injection attack. For example:

Collapse

string surname = this.surnameTb.Text.Replace("'", "''");
string sql = "Update Users SET Surname='"+surname+"' "+
    "WHERE id="+userID;

All of the previous injection attack examples would cease to work given a scenario like this.

However, many applications need the user to enter numbers and these don't need to have the apostrophes escaped like a text string. If an application allows the user to review their orders by year, the application may execute some SQL like this:

Collapse

SELECT * FROM Orders WHERE DATEPART(YEAR, OrderDate) = 1996

And in order for the application to execute it, the C# code to build the SQL command might look like this:

Collapse

string sql = "SELECT * FROM Orders WHERE DATEPART(YEAR, OrderDate) = "+
    this.orderYearTb.Text);

It becomes easy to inject code into the database again. All the attackers need to do in this instance is start their attack with a number, then they inject the code they want to run. Like this:

Collapse

0; DELETE FROM Orders WHERE ID = 'competitor';--

It is therefore imperative that the input from the user is checked to determine that it really is a number, and in the valid range. For instance:

Collapse

string stringValue = orderYearTb.Text;
Regex re = new Regex(@"\D");
Match m = re.Match(someTextBox.Text);
if (m.Success)
{
    // This is NOT a number, do error processing.

}
else
{
    int intValue = int.Parse(stringValue);
    if ((intValue < 1990) || (intValue > DateTime.Now.Year))
    {
        // This is out of range, do error processing.

    }
}

Second-Order Attacks

A second-order attack is one where the data lies dormant in the database until some future event occurs. It often happens because once data is in the database, it is often thought of as being clean and is not checked again. However, the data is frequently used in queries where it can still cause harm.

Consider an application that permits the users to set up some favourite search criteria. When the user defines the search parameters, the application escapes out all the apostrophes so that a first-order attack cannot occur when the data for the favourite is inserted into the database. However, when the user comes to perform the search, the data is taken from the database and used to form a second query which then performs the actual search. It is this second query which is the victim of the attack.

For example. If the user types the following as the search criteria:

Collapse

'; DELETE Orders;--

The application takes this input and escapes out apostrophe so that the final SQL statement might look like this:

Collapse

INSERT Favourites (UserID, FriendlyName, Criteria)
VALUES(123, 'My Attack', ''';DELETE Orders;--')

which is entered into the database without problems. However, when the user selects their favourite search, the data is retrieved to the application, which forms a new SQL command and executes that. For example, the C# code might look like:

Collapse

// Get the valid user name and friendly name of the favourite

int uid = this.GetUserID();
string friendlyName = this.GetFriendlyName();

// Create the SQL statement to retrieve the search criteria

string sql = string.Format("SELECT Criteria FROM Favourites "+
    "WHERE UserID={0} AND FriendlyName='{1}'",
    uid, friendlyName);
SqlCommand cmd = new SqlCommand(sql, this.Connection);
string criteria = cmd.ExecuteScalar();

// Do the search

sql = string.Format("SELECT * FROM Products WHERE ProductName = '{0}'",
    criteria);
SqlDataAdapter da = new SqlDataAdapter(sql, this.Connection);
da.Fill(this.productDataSet);

The second query to the database, when fully expanded, now looks like this:

Collapse

SELECT * FROM Products WHERE ProductName = ''; DELETE Orders;--

It will return no results for the expected query, but the company has just lost all of their orders.

Parameterised Queries

SQL Server, like many database systems, supports a concept called parameterised queries. This is where the SQL Command uses a parameter instead of injecting the values directly into the command. The particular second-order attack above would not have been possible if parameterised queries had been used.

Where the application developer would have constructed a SqlCommand object like this:

Collapse

string cmdText=string.Format("SELECT * FROM Customers "+
    "WHERE Country='{0}'", countryName);
SqlCommand cmd = new SqlCommand(cmdText, conn);

A parameterised query would look like this:

Collapse

string commandText = "SELECT * FROM Customers "+
    "WHERE Country=@CountryName";
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.Parameters.Add("@CountryName",countryName);

The value is replaced by a placeholder, the parameter, and then the parameter's value is added to the Parameters collection on the command.

While many second-order attacks can be prevented by using parameters, they can only be used in places were a parameter is permitted in the SQL statement. The application may return a variable sized result set based on user preference. The SQL statement would include the TOP keyword in order to limit the result set, however, in SQL Server 2000, TOP can only accept literal values so the application would have to inject that value into the SQL command to obtain that functionality. For example:

Collapse

string sql = string.Format("SELECT TOP {0} * FROM Products", numResults);

Using Stored Procedures

Stored Procedures add an extra layer of abstraction in to the design of a software system. This means that, so long as the interface on the stored procedure stays the same, the underlying table structure can change with no noticeable consequence to the application that is using the database. This layer of abstraction also helps put up an extra barrier to potential attackers. If access to the data in SQL Server is only ever permitted via stored procedures, then permission does not need to be explicitly set on any of the tables. Therefore, none of the tables should ever need to be exposed directly to outside applications. For an outside application to read or modify the database, it must go through stored procedures. Even though some stored procedures, if used incorrectly, could potentially damage the database, anything that can reduce the attack surface is beneficial.

Stored procedures can be written to validate any input that is sent to them to ensure the integrity of the data beyond the simple constraints otherwise available on the tables. Parameters can be checked for valid ranges. Information can be cross checked with data in other tables.

For example, consider a database that has the user details for a website, this includes the user name and password. It is important that an attacker is unable to get a list of passwords or even one password. The stored procedures are designed so that a password can be passed in, but it will never put a password in any result set. The stored procedures for registering and authenticating a user for the website might be:

• RegisterUser
• VerifyCredentials
• ChangePassword

RegisterUser takes the user name and password as parameters (possibly along with other information that is necessary for registering on the website) and returns the UserID.

VerifyCredentials would be used for logging into the site by accepting the user name and the password. If there is a match the UserID is returned, if not then a NULL value.

ChangePassword would take the UserID, the old password and the new password. If the UserID and the password match, the password can be changed. A value that indicates success or failure is returned.

The above example shows that the password is always contained in the database and is never exposed.

Stored Procedure Caveat

While stored procedures seem to be a wonderful panacea against injection attacks, this is not necessarily the case. As mentioned above, it is important to validate data to check that it is correct and it is a definite benefit of stored procedures that they can do this; however, it is doubly important to validate data if the stored procedure is going to use EXEC(some_string) where some_string is built up from data and string literals to form a new command.

For instance, if the stored procedure is to modify the data model of the database, such as creating a table, the code may be written as follows:

Collapse

CREATE PROCEDURE dbo.CreateUserTable
    @userName sysname
AS
    EXEC('CREATE TABLE '+@userName+
        ' (column1 varchar(100), column2 varchar(100))');
GO

It is obvious that whatever @userName contains will be appended to the CREATE statement. An attacker could inject into the application some code that sets the user name to be:

Collapse

a(c1 int); SHUTDOWN WITH NOWAIT;--

which will immediately stop the SQL Server without waiting for other requests to complete.

It is important to validate the input to ensure that no illegal characters are present. The application could be set to ensure that spaces are not permitted as part of the user name and this could be rejected before it ever got as far as constructing the CREATE statement.

If the stored procedure is going to construct a SQL command based on an existing object, such as a table or view, then it should check that such an object exists. For instance:

Collapse

CREATE PROCEDURE dbo.AlterUserTable
    @userName sysname
AS
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'dbo'
        AND TABLE_TYPE = 'BASE TABLE'
        AND TABLE_NAME = @userName)
    BEGIN
        // The table is known to exist
        // construct the appropriate command here
    END
GO

Error Messages

Error messages are useful to an attacker because they give additional information about the database that might not otherwise be available. It is often thought of as being helpful for the application to return an error message to the user if something goes wrong so that if the problem persists they have some useful information to tell the technical support team. Applications will often have some code that looks like this:

Collapse

try
{
    // Attempt some database operation

}
catch(Exception e)
{
    errorLabel.Text = string.Concat("Sorry, your request failed. ",
        "If the problem persists please report the following message ",
        "to technical support", Environment.Newline, e.Message);
}

A better solution that does not compromise security would be to display a generic error message that simply states an error has occurred with a unique ID. The unique ID means nothing to the user, but it will be logged along with the actual error diagnostics on the server which the technical support team has access to. The code above would change to something like this instead:

Collapse

try
{
    // Attempt some database operation

}
catch(Exception e)
{
    int id = ErrorLogger.LogException(e);
    errorLabel.Text = string.Format("Sorry, your request Failed. "+
        "If the problem persists please report error code {0} "
        "to the technical support team.", id);
}

Summary

• Encrypt sensitive data.
• Access the database using an account with the least privileges necessary.
• Install the database using an account with the least privileges necessary.
• Ensure that data is valid.
• Do a code review to check for the possibility of second-order attacks.
• Use parameterised queries.
• Use stored procedures.
• Re-validate data in stored procedures.
• Ensure that error messages give nothing away about the internal architecture of the application or the database.

References

• SQL Injection Attacks by Example shows the thought process of someone attempting a security review for the first time on a customer's intranet site.
• How To Use Forms Authentication with SQL Server 2000 demonstrates creating an authentication mechanism that stores passwords as a salted hash value.

source: http://www.codeproject.com/KB/database/SqlInjectionAttacks.aspx