SQL Injection / Date:
Start time:

This activity explores SQL injection and its mitigation.

You may find it easier to sketch some answers on a separate piece of paper, and use that to come up with your report.

Before you start, complete the form below to assign a role to each member.

If you have 3 people, combine Manager & Reflector.

Time: / Date:
Team Roles / Team Member
Recorder: records all answers & questions, and provide copies to team & facilitator (instructor)
Speaker: Talk to facilitator and other teams
Manager: keeps track of time and makes sure everyone contribute appropriately
Reflector: considers how the team could work and learn more effectively

SQLInjection Activity

Activity 1: Injection Basics (10 minutes)

Injection attacks trick an application into including unintended commands in the data send to an interpreter.

Interpreters Interpret strings as commands such as SQL, shell (cmd.exe, bash)

Critical Thinking Questions:

  1. What does a user/attacker see at the webpage? Where can an attacker enter exploit data/input?
  1. Which entity builds user string input and send SQL query to Database server?
  1. Which entity executes query including exploit and send back to web server and then user?

Activity 2: How does injection attack occur? (40 minutes)

Assume the web server includes the following code

sqlString = “select USERID from USER where USERID = ` $userId` and PWD = ` $password`”

result = GetQueryResult(sqlString)

if(result = “”) then

userHasBeenAuthenticated = False

else

userHasBeenAuthenticated = True

end if

Here,$userIdand$passwordare the valuessubmitted by the user, and the query statement provides the quotation marks that set it as a literal string.

Critical Thinking Question

  1. If a user entersUTCas userId andchattanooga’as password, what is theconstructed SQL statement which is also the value of sqlString from the above code?
  1. Will the constructed SQL be executed? Why and Why not?
  1. After enterUTCas userId andchattanooga’you may observe different system responses: a) the SQL parser find the extra quote mark and aborts with a syntax error and return server error, or b) email address is unknown or We don't recognize your email address. This distinction will be very useful when trying to guess the structure of the query. Which one means that user input is not being sanitized properly and that the application is ripe for exploitation?
  1. What is the resulting SQL query statement if a user enters User ID as ` OR ``=`and Password as `OR ``=`? What is the result of this query or what is the value of sqlString? Does this input allow user to log in? And why or Why not?
  1. What is the resulting SQL query statement if a user enters User ID as ` OR ``=`` -- and Password as abc? What is the result of this queryor what is the value of sqlString? Does this input allow user to log in? Andwhy or Why not?
  1. Explain why the case in question 7 and 8 construct injection attacks.

Critical Thinking questions:

  1. We know that the server will query a table for results.Schema field mapping is trying to guess column names of a table. Some common column names include email, userid, passwd, login_id, full_name. Let’s guess email as the name of one column. The following text in the box is entered by an attacker trying to guess if ”email” is one of column names. The system may respond with a) a server/system error, or b) “email address unknown”. Which one reveals that guessing is right? Why do we use AND instead of OR here?

SELECT fieldlist

FROM table

WHERE field = ' x' AND email IS NULL; --';

  1. If you are an attacker, can you use similar ways to guess if “userid” and “password” are columns names? Pleasecraft some texts to be entered in the box to achieve your goal.

Assume that we have figured out that the target table has the following column names: “email”, “passwd”, “login_id”, and “full_name”. We are trying to figure out name of the target table.First, let’s look at a standalone query of

SELECT COUNT(*) FROM guesstablename

Returns the number of records in that table, and of course fails if the table name is unknown.We can build this into our string to probe for the table name:

SELECT email, passwd, login_id, full_name

FROM table

WHERE email = ' x' AND 1=(SELECT COUNT(*) FROM tabname); --';

We don't care how many records are there, of course, only whether the table name is valid or not. By iterating over several guesses, we eventually determined that members was a valid table in the database.

  1. What does it mean if the following returns "Email unknown"?

SELECT email, passwd, login_id, full_name

FROM members

WHERE email = ' x' AND members.email IS NULL; --';

The next step is to guess some user names. At this point we have a partial idea of the structure of the members table. The first place to start, of course, is the company's website to find who is who: the "About us" or "Contact" pages often list who's running the place. Many of these contain email addresses, but even those that don't list them can give us some clues which allow us to find them with our tool.

Critical Thinking questions:

  1. Can you craft an input to be entered in the box, which allows us to do partial matches of names or email address in the database? We are guessing values of the target table now. Thinkof a query that uses the LIKE clause, allowing us to do partial matches of names or email addresses in the database? Note: You can use LIKE clause in your guess and this requires iteration to refine further guessing.

SELECT email, passwd, login_id, full_name

FROM members

WHERE email = ' ';

Bruteforce password guessing

One can certainly attempt bruteforce guessing of passwords at the main login page, but many systems make an effort to detect or even prevent this. There could be logfiles, account lockouts, or other devices that would substantially impede our efforts, but because of the nonsanitized inputs, we have another avenue that is much less likely to be so protected. We'll instead do actual password testing in our snippet by including the email name and password directly. In our example, we'll use our victim, and try multiple passwords.

Critical Thinking questions:

  1. Can you craft an input to be entered in the box in order to guess if password is hello123?

SELECT email, passwd, login_id, full_name

FROM members

WHERE email = ' ';

  1. Can you describe what the following statement in the box intend to do?

SELECT email, passwd, login_id, full_name

FROM members

WHERE email = ' x'; DROP TABLE members; --'; -- Boom!

  1. Given that we know the partial structure of the members table and column names as “email”, “passwd”, “login_id”, and “full_name”, it seems like a plausible approach to attempt adding a new record to that table: if this works, we'll simply be able to login directly with our newly inserted credentials. Can you think out an input that can insert a member?

Activity 3: Mitigations of SQL Injection (10 minutes)

We believe that web application developers often simply do not think about "surprise inputs", but security people do (including the bad guys), so there are three broad approaches that can be applied here.

We could sanitize the input or Escape/Quotesafe the input. Getting quotes right is notoriously difficult. The language of the web is full of special characters and strange markup (including alternate ways of representing the same characters), and efforts to authoritatively identify all "bad stuff" are unlikely to be successful.

Instead, rather than "remove known bad data", it's better to "remove everything but known good data": this distinction is crucial.

Critical Thinking questions:

  1. Simply removing the quotes" is problematic because even "regular" characters can be troublesome.Some applications require an integer ID value is being compared against the user input (say, a numeric PIN). Does removing quotes prevent the following injection attacks? Why?

SELECT fieldlist

FROM table

WHERE id = 23 OR 1=1; -- Boom! Always matches!

  1. Some input does contain quote which is necessary to consider, such as a valid name “O’’Reilly”. Filtering out single quote from “O’’Reilly” can give correct input “O’Reilly”. Discuss your team member for the following case.

SELECT fieldlist

FROM customers

WHERE name = ' Bill O''Reilly'; -- works OK

  1. If a hacker knows that single quote will be filtered out, he/she can use the following way to bypass the filter. Example, why the approach offiltering out single quote will not work in the following case.

SELECT fieldlist

FROM customers

WHERE name = ' \''; DROP TABLE users; --'; -- Boom!

SQL Injection Defense Option #1: Use of Prepared Statements (Parameterized Queries)

Critical Thinking questions:

  1. Explain why the following (Java) example is UNSAFE, and would allow an attacker to inject code into the query that would be executed by the database.

String query = "SELECT account_balance FROM user_data WHERE user_name = "

+ request.getParameter("customerName");

try {

Statement statement = connection.createStatement( … );

ResultSet results = statement.executeQuery( query );

}

  1. Explain why the following code example using PreparedStatement is safe.

String custname = request.getParameter("customerName"); // This should REALLY be validated too

// perform input validation to detect attacks

String query = "SELECT account_balance FROM user_data WHERE user_name =? ";

PreparedStatement pstmt = connection.prepareStatement( query );

pstmt.setString( 1, custname);

ResultSet results = pstmt.executeQuery( );

SQL Injection Defense Option #2: Use of Stored Procedures (after class research)

Stored procedures have the same effect as the use of prepared statements when implemented safely* which is the norm for most stored procedure languages. They require the developer to just build SQL statements with parameters which are automatically parameterized unless the developer does something largely out of the norm. The difference between prepared statements and stored procedures is that the SQL code for a stored procedure is defined and stored in the database itself, and then called from the application. Both of these techniques have the same effectiveness in preventing SQL injection so your organization should choose which approach makes the most sense for you.

SQL Injection Defense Option #3: Escaping all User Supplied Input (after class research)

This second technique is to escape user input before putting it in a query. However, this methodology is frail compared to using parameterized queries and we cannot guarantee it will prevent all SQL Injection in all situations. This technique should only be used, with caution, to retrofit legacy code in a cost effective way. Applications built from scratch, or applications requiring low risk tolerance should be built or re-written using parameterized queries.

This technique works like this. Each DBMS supports one or more character escaping schemes specific to certain kinds of queries. If you then escape all user supplied input using the proper escaping scheme for the database you are using, the DBMS will not confuse that input with SQL code written by the developer, thus avoiding any possible SQL injection vulnerabilities.

References:

SQL Injection Attacks by Example, Steve Friedl's Unixwiz.net Tech Tips

UTCYang&YuanNC A&T