SQL Injection and Web-based Security by chenmeixiu


									SQL Injection
Who Am I?

Sean Taylor
Computer Science major
Software developer
Web developer
Amateur hacker
Who I Work For

 Streaming Media Hosting, Inc.
 Currently looking for a Senior Network and
  System Administrator - contact
  smtaylor@csupomona.edu for more
Software Used

 PHP 5
 MySQL 5
 Apache 2
 Vim
  (it‟s better than Emacs)
What is SQL Injection?
What is SQL Injection?

Not a software flaw
Arises with concatenation of dynamic
Can be performed on anything using an
 SQL-based language, such as MySQL,
 PostgreSQL, MS-SQL, and others.
What is SQL Injection?

$query = “SELECT * FROM my_table WHERE id = $_GET[„id‟]”;
What is SQL Injection?
Attack Techniques
Attack Techniques

Testing for injectable variables
   Toss in an apostrophe („)
   Mess with integers
   Throw in an always-true statement
Attack Techniques

Throwing in apostrophes

    SELECT * FROM my_table WHERE id = „

    You have an error in your SQL syntax; check the
    manual that corresponds to your MySQL server version
    for the right syntax to use near ''' at line 1
Attack Techniques

Messing with integers

    SELECT * FROM my_table WHERE id = 1 hello world!

    You have an error in your SQL syntax; check the
    manual that corresponds to your MySQL server version
    for the right syntax to use near ‟hello world!' at
    line 1
Attack Techniques

Always True Statements

    SELECT * FROM my_table WHERE id = 2 OR 1=1 -- AND …

    Returns everything in my_table.
Attack Techniques

The UNION keyword
   If you have a laptop with you, point yourself to
    Attack Techniques

     The UNION keyword
…?showall=no' UNION SELECT social FROM social_securities --%20

          Returns:
               quote in database:
Attack Techniques

   http://www.technotaylor.net/secure/example2.php
     Attack Techniques

      SELECT AS
…?id=30 UNION SELECT social AS quote FROM social_securities --%20

           Returns:
Attack Techniques

Multi-return vs. Single return
Obviously can‟t perform SELECT *
How do we get around this for multiple
 values in a SELECT statement?
  Attack Techniques

   Boolean operators can be performed
    practically everywhere
… FROM social_securities WHERE social > „111-11-1111‟ --%20

        Returns:
Defense Techniques
Defense Techniques

Escaping quotes are not enough
   addslashes() and mysql_escape_string() in
    PHP won‟t save you!

   http://www.technotaylor.net/secure/example3.php
 Defense Techniques

  C-String Attack
       Replace any string in your injection query
        with a CONCAT of CHARs
… SELECT FROM social_securities
WHERE social > CONCAT(CHAR(49), CHAR(49), CHAR(49), CHAR(45)…)

       Returns:
Defense Techniques

Take advantage of on-the-fly
   Some databases, like MySQL, do on-the-
    fly conversions. For example, a string can
    be converted easily to an integer on the fly
    and vice-versa without needing to strictly
   When strings are properly escaped, there
    is no breaking out of them.
Defense Techniques

Cast your variables at the software layer
   PHP has a function called settype(), so you
    can be sure that if you‟ve received a string
    it will always be an int
Defense Techniques

If you have a pre-determined set of
 inputs, use them to your advantage.
   If you know the values are going to be
    “red”, “blue”, or “green”, create a switch-
    case declaration to verify the variables and
    dump anything else that comes in as
Defense Techniques

If you have a pre-determined length of
 input, limit the input length at the
 software layer
   If you only have input that‟s 1-8, limit the
    length of the input to simply one character.
Defense Techniques

Use prepared statements when possible
   PHP comes with an awesome SQL
    security suite called PearDB
Defense Techniques

If prepared statements aren‟t a possible
 solution, use SQL libraries built for this
   SafeSQL
      http://www.phpinsider.com/php/code/SafeSQL/

   StrictDB
      http://www.technotaylor.net/strictdb/
General Security Tips
General Security Tips

 PHP specific
   Never, ever, ever, EVER use eval()
   Don‟t use $_REQUEST -- use $_GET, $_POST
    and $_COOKIE instead.
   Turn off magic_quotes_gpc-- they‟re useless and
      Add “php_flag magic_quotes_gpc off” to your .htaccess
   If you‟re not debugging, turn off error reporting
    with error_reporting(0)
General Security Tips

 Cross-site scripting (aka XSS)
   Problem: user can put in HTML tags
   Solution: strip and replace all HTML-specific
        &   =>   &
        “   =>   "
        „   =>   '
        <   =>   &lt;
        >   =>   &gt;
   PHP has a function that does this for you--
    htmlspecialchars(“string goes here”);
General Security Tips

Directory traversal
   Problem: input argument is a file to be read
    on the server.
   Solution: don‟t do that because it is very
      If someone puts in an argument for the file
       called “../../../etc/passwd”, congratulations,
       you‟ve compromised your server by showing
       off the password file!
General Security Tips

 File uploads
   MIME types are never to be trusted.
   Neither are file extensions.
   To verify a file‟s actual type, you may have to go
    as far as open the file and inspect its header
   Beware how you handle filenames: browsers can
    be manipulated to send malicious file extensions,
    such as “my_picture.jpg; cat ../../../etc/passwd”
General Security Tips

Password storing
   Don‟t use two-way encryption
   MD5 has been broken-- use SHA1
   Don‟t forget to salt your hashes!
General Security Tips

Salting your hashes
   Salting is appending or prepending an
    incoming a password with an 8-16 string of
    random characters and hashing the result.
   Salting keeps people from performing
    dictionary attacks on your database.
   http://www.technotaylor.net/secure/example4.php?showall=yes
General Security Tips

 An SQL-injected dictionary hash-attack
…?showall=no‟ UNION SELECT username FROM users WHERE
hash = (SELECT hash FROM users WHERE username =

     Returns:
          registered users in database:
More Information
More Information

 Good „ol Wikipedia
   http://en.wikipedia.org/wiki/SQL_injection
 Toorcon presentation on SQL injection
   http://video.google.com/videoplay?docid=5773019
 PHP Security whitepaper
   http://www.acunetix.com/websitesecurity/php_whit
   The End
You have just lost The Game.

To top