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

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.

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.

In Resources on web application security there are several resources dedicated to DB. For instance project OWASP has:

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…

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:

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.

I think that I’m not doing any sort of preparing or binding. :goof:
Basically, in a library, I define my database, password, etc. by using:

$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:

$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?

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 :expressionless:

This is what querying a database without prepared statements looks like (where there is the risk of SQL injection):

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);

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! :slight_smile:

Enjoy your evening…

-Eric