The Issue

The Issue

The issue is the same issue I pointed out in the last two chapters, and it's the same issue in the next chapter: misplaced trust; trusting that the user has given your application well-formed data, when in fact the user has not. Let me give an example.

Many applications include code that looks something like the following. Go on, admit it you have constructed SQL strings like this:

string sql = "select * from client where name = '" + name + "'"

The variable name is provided by the user. The problem with this SQL string is that the attacker can piggyback SQL statements in the name variable. Imagine input where name = Blake, which builds this totally benign SQL statement:

select * from client where name = 'Blake'

However, what if an attacker enters this: Blake' or 1=1 --. The following malicious statement is built:

select * from client where name = 'Blake' or 1=1 --

This statement will return all columns in table client for any row where the name column is Blake. It will also return any row that satisfies the 1=1 clause. Unfortunately for the good guys but good news for the bad guys is the fact that 1=1 is true for every row in the table, so the attacker sees all rows in the table. If you don't think this is bad, imagine that the database table schema looks like that in Figure 12-1.

. A client table schema containing credit card information.

The last part of the query is the -- characters. These characters are a comment operator, which makes it easier for an attacker to build a valid, but malicious SQL statement. When the attacker is happy that the SQL statement or statements are complete, he places a comment operator at the end to comment out any characters added by the programmer.

NOTE
The comment operator -- is supported by many relational database servers, including Microsoft SQL Server, IBM DB2, Oracle, PostgreSQL, and MySql.

The example I just showed is called SQL injection. This is an attack that changes the logic of a valid SQL statement in this case, by adding an or clause to the statement. Not only can you alter a single SQL statement with this technique, you can add additional SQL statements and also call functions and stored procedures.

By default, some database servers allow a client application to perform more than one SQL statement at once. For example, in SQL Server, you can issue

select * from table1 select * from table2

and the two SQL select statements execute.

Attackers can have more fun than simply getting two SQL queries to execute; SQL engines include support for data manipulation constructs, such as the ability to create, delete (called drop), and update database objects such as tables, stored procedures, rules, and views. Take a look at the following name an attacker could enter:

Blake' drop table client --

This builds a SQL query that queries for the name, Blake, and then drops or deletes the client table.

While demonstrating how to manipulate databases by using SQL injection at the Professional Developer's Conference in 2001, I accidentally deleted my core demonstration table. Even though I ruined my demo, I think I made the point!

Now, you're probably thinking how on earth can a user on the Internet, connecting to a back-end database from a Web server or Web service, possibly delete a table from a database. Well, look at this code:

string Status = "No"; string sqlstring = ""; try { SqlConnection sql= new SqlConnection( @"data source=localhost;" +  "user id=sa;password=password;"); sql.Open(); sqlstring="SELECT HasShipped" +  " FROM detail WHERE ID='" + Id + "'"; SqlCommand cmd = new SqlCommand(sqlstring,sql); if ((int)cmd.ExecuteScalar() != 0) Status = "Yes"; } catch (SqlException se) { Status = sqlstring + " failed\n\r"; foreach (SqlError e in se.Errors) { Status += e.Message + "\n\r"; } } catch (Exception e) { Status = e.ToString(); }

Can you spot the security flaws in this C# code? The first is obvious: the code creates SQL statements by using string concatenation, which will lead to SQL injection attacks. But there's more. The connection identity from this Web service code to the back-end database is sa, the sysadmin account in SQL Server. You should never make a connection to any database server by using such a dangerous account; sa is to SQL Server what SYSTEM is to Windows NT and later. Both are, by far, the most capable and potentially damaging accounts in their respective systems. The same database admin account in Oracle is named internal.

The next error is the password to sa let's just say it could be broken by a six-year-old child! In addition, the fact that it's embedded in the code is even worse. And here's another error: if the code that handles the SQL connection fails for any reason, a complete description of how the failure occurred is given to the attacker, including what the SQL statement looked like when it failed. This aids the attacker immensely, as he can see the source of his errors.

Now let's move on to remedies for such poor programming, and then we'll look at real remedies.