SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Testing Database Security

    Hello,

    I think that it's great that SitePoint has a post on SQL injection -- it's opened my eyes to some very troubling possible problems in databases security. Unfortunately, however, it seems like that are many different syntaxes for accessing one's database so that from a practical standpoint, it might be really helpful, if someone could give me (and others!), a "TOP 10 Attempts at breaking into a database" list. In other words, are there good "bad queries" that one could do to see if we have database vulnerabilities (for example, the infamous "or 1=1")? Then, if we do have the vulnerabilities, we can figure out what parts are vulnerable, why they're vulnerable, and then figure out how to improve our specific site's issues.

    Thanks!

    -Eric

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You don't need to approach it like that. If you always use parameterized queries (prepared statements, libraries built on top of them like PDO) rather than concatenate a string together yourself then there's no vulnerability to SQL injection. You don't have to test against 10 example queries, it's simply not possible if the code is not vulnerable to that class of attack.

  3. #3
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    What Dan said. With the caveat that doing things like using stored procedures which generate dynamically concatenated SQL strings then execute them under the hood open said vulnerability right up.

  4. #4
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In Resources on web application security there are several resources dedicated to DB. For instance project OWASP has:
    https://www.owasp.org/index.php/SQL_Injection

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the quick responses! I kind of dove head first into my first database application and have been using the Zend_framework (because it happened to be what the book I purchased was using) to do all of my database queries. How would I know if I'm using "prepared statements", or a "library"? Sorry about the newbie questions about the lingo...

  6. #6
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,788
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by kreut View Post
    How would I know if I'm using "prepared statements", or a "library"?
    Are your database calls being done with a prepare statement that defines the query and then a separate bind statement after it that attaches the data fields? eg. using mysqli you could use prepare and bind like this:

    Code:
    if ($stmt = $mysqli->prepare(
    "SELECT address, age FROM friends WHERE firstname=? AND lastname=?")) {
     $stmt->bind_param("ss", $firstname, $lastname);
    The PDO equivalents look similar and will contain the words prepare and bind.

    If it uses mysql_query or mysqli_query to do the database accesses then it isn't using prepare and is vulnerable to injection.

    If you are calling a library to do the database accesses for you then you should be able to tell which it uses by whether it expects you to pass the data fields to it separately from the query you want it to run.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  7. #7
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I think that I'm not doing any sort of preparing or binding.
    Basically, in a library, I define my database, password, etc. by using:
    PHP Code:
    $dbWrite = new Zend_Db_Adapter_Pdo_Mysql($write); 
    where the $write variable contains my DB information. A typical insertion into the database would then look like:

    PHP Code:
    $data = array('course_id' => 'school_id',
                          
    'user_id' => $user_id);     
            
    $inserted $dbWrite->insert('class_enrollment',$data
    Based on what you just wrote is it then correct that I should be "preparing" and "binding" each time that I access my database --- regardless of whether I insert, update, delete, or just read from it?

  8. #8
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Your "Zend DB Adapter" is using PDO (it's in the class name), and it's using prepared statements. You're passing in the data separately from the query (you're actually not writing the SQL query, some abstraction is for you).

    This is a bit of a problem with using someone else's tools before you've learned the language they're built on... you don't really know what they're doing :|

    This is what querying a database without prepared statements looks like (where there *is* the risk of SQL injection):
    PHP Code:
    mysql_connect('localhost''username''password');
    mysql_select_db('school_db');

    $first_name $_POST['first_name'];
    $last_name $_POST['last_name'];

    $sql "INSERT INTO students (first_name, last_name) VALUES ('$first_name', '$last_name')";
    mysql_query($sql); 

  9. #9
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Dan,

    Thanks for the email. And, I couldn't agree with you more that I most definitely should have learned the rules of the road before using the library. To be fair to myself, however, I actually thought that I was doing things the "right" way. To learn this stuff, I bought David Powers' "DW CS5 with PHP: Training from the Source" and I was under the impression by reading the book that "Zend" was the way to go as opposed to mastering first principles first (in a similar way that I thought that I could just use Dreamweaver without REALLY knowing what it was doing). The perils of the newbie!

    Enjoy your evening....

    -Eric


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •