Auditing SQL

by Rudy P
This week I will be blogging about the journal entry “Auditing a Batch of SQL Queries” by Rajeev Motwani, Shubha U. Nabar, and Dilys Thomas of Stanford University. This Journal entry talked about ways SQL Queries are audited and how to determine suspicious SQL queries. The Journal makes mention of a command AUDIT, which I had never seen before. They use it an example:

“AUDIT zipcode
FROM Patients p
WHERE p.disease = ‘high blood pressure’ ” (Motwani, 2007).

In this example the command Audit is interested in finding out if the zipcode of any Patient with high blood pressure was revealed. This command is used to help determine whether a query is suspicious such as the example considering keeping the phone numbers of patients completely  private. In this example a query asks for the size of the database, this does not seem suspicious initially, however this reveals the amount of phone numbers the database hold. Thus this can be considered suspicious if “perfect privacy” is wanted.

The Journal entry was a it technical at points especially when speaking about certain theorems and algorithms to find queries that would disclose private information such as:

“A tuple t ∈ T is indispensable to a query Q
if the presence or absence of t makes a difference to the result of Q, i.e. πCQ(σPQ (T )) =
πCQ(σPQ (T − {t}))” (Motwani, 2007).

After rereading some of these theorems a few times I could make sense of them and how they would work in the logic of creating a database. Security of the information in a database is vital and if something should remain perfectly private in the database, then there should be no way to access said information. The Journal does a good job of showing how multiple queries could lead to the disclosure of private information such as in the following example where they are attempting to keep patients medical history private:

“SELECT zipcode

FROM Patients p

WHERE p.disease = ‘diabetes’

FROM Patients p
WHERE p.zipcode = 94305″ (Motwani, 2007).

These two statements independently would not reveal anything, however combined these statements could reveal a patient and a disease the patient has. Examples like this shows how important it is to keep security and the protection of private information in mind when create databases.


Motwani, R.; Nabar, S.U.; Thomas, D.; , “Auditing a Batch of SQL Queries,” Data Engineering Workshop, 2007 IEEE 23rd International Conference on , vol., no., pp.186-191, 17-20 April 2007

3 thoughts on “Auditing SQL

  • November 11, 2012 at 6:13 pm

    I must say I like your article very much. I feel many people in our class have been talking about SQL injection attacks but I haven’t seen any examples of them. This definitely clarifies what exactly these hackers are doing. Its interesting how to see how hackers are manipulating this data to fit their needs. Great article!

  • November 11, 2012 at 6:30 pm

    It is amazing to see how easily sensitive data can be exposed from a database once a hacker has gained a foothold into the server. The article I read this week talked about SQL injection and how it can be used to reveal data by tricking the database into running a malicious code that a hacker wanted. This sounds just like the code that would be used in the injection attempt to reveal the kind of data that the hacker was looking for. Interesting read and very informative as to how a hacker would craft a series of commands to find what they wanted.

  • November 13, 2012 at 12:28 pm

    I agree this is a great example of an SQL attack. It makes sense that they could potentially find data that was meant to be secret by simply throwing query’s at the database. Hopefully we will learn how to prevent such attacks.

    It always made me wonder though, what a person would do with such information. I mean obviously things like SSN’s and credit card info should be kept out of the wrong persons hands, but I would imagine most of the data which gets compromised wouldn’t have much use to an outside person. Just a thought.

Comments are closed.