Question

Activity 3: How does injection attack occur? (30 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, $userId and $password are the values submitted 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 enters UTC as userId and chattanooga’ as password, what is the constructed SQL statement which is also the value of sqlString from the above code?

2. Will the constructed SQL be executed? Why and Why not?

3. After enter UTC as userId and chattanooga’ 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?

4. 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?

5. 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 query or what is the value of sqlString? Does this input allow user to log in? And why or Why not?

6. Explain why the case in question 7 and 8 construct injection attacks.

Answer 1

1) If user enters UTC as userid and chattanooga as password, then following sqlString will be generated.

select USERID from USER where USERID = 'UTC' and PWD = 'chattanooga''

2) The sql will be executed and if there is UTC userid with chattanooga' password, then script will set userHasBeenAuthenticated = True, because once the sql query executed it will return resultset. if there is not such userid, then it will set that to False.

Only thing is to keep in mind that you need to check whether special symbols like ' is allowed or not. as it is used for sql injection, so may databases disabled that using javascript. if so, then query will result in error.

3) Yes, as stated in answer 2 that single quote mark can create problem based on the configuration of database and application which is sending that data to backend. But mostly it will throw error is that double quote is prevented in databased because may application using javascript will never allow to use it. Sometimes, it will also throw error that is mentioned in the statement that email address not found.

4) User ID as ` OR ``=` and Password as `OR ``=`

then generated query will look like

select USERID from USER where USERID = '' OR ''='' and PWD = ' OR ''=''

since the generated query is SQL Injection, it will login the user.

5) User ID as ` OR ``=`` -- and Password as abc

select USERID from USER where USERID ='' OR ''=''-- and PWD=''=''--

The key thing here is that the double-dash sequence -- is a comment indicator in SQL, and means that the rest of the query is interpreted as a comment. This effectively removes the remainder of the query, so it no longer includes AND PWD=''=''--. This means that all USERID will be fetched....

Last two answers are case of successful SQL Injection. It will confuse the database engine to force it to give all rows and everything from database regardless or you are authorized or not.

Most important way to prevent this is to use proper input validation during the passing values to backend one of the best way is to use java script.

Thanks.