Government Security
Network Security Resources

Jump to content

Photo

Php & Pdo: A Solution Against Sqli ?

- - - - - php sql
  • Please log in to reply
2 replies to this topic

#1 Booster2ooo

Booster2ooo

    Sergeant

  • Members
  • 247 posts

Posted 25 May 2010 - 01:42 AM

Hi there,

I've been coding PHP for a year or two and, recently, I read some papers about PDO and prepared statements. It seems that prepared statements (which are going to be the stantard in PHP 6?) are 1. very effective in term of execution time and ressources loading and 2. unaffected by SQLi. As I'm still a big noob in SQL, I didn't made the test and I believed the things I read.

So, do you guys use prepared statements? Is it that effective for time/ressources ? Is it really secure ?

#2 Booster2ooo

Booster2ooo

    Sergeant

  • Members
  • 247 posts

Posted 25 May 2010 - 05:07 AM

Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack. Normally when you are dealing with an ad hoc query, you need to be very careful when handling the data that you received from the user. This entails using functions that escape all of the necessary trouble characters, such as the single quote, double quote, and backslash characters. This is unnecessary when dealing with prepared statements. The separation of the data allows MySQL to automatically take into account these characters and they do not need to be escaped using any special function.

src: http://dev.mysql.com...statements.html


...When the data is handed to PDO like this, it then either passes the data on to the database driver directly, or builds the query internally in a safe manner with any potentially malicious data encoded or escaped. As you can see, this is an easy way around the problem of SQL injection.

However, prepared statements with PDO aren't all puppies and rainbows. Using prepared statements can introduce a number of interesting caveats of which developers should be aware. For example, in the MySQL client API prepared statements can not execute certain types of queries[1] and they do not use the query cache[1][2] which may have an impact on your application's performance.

The inherent security in using prepared statements sounds great, but developers should not let PDO and other abstraction layers/prepared statement implementations lull them into a false sense of security. Untrusted data should always be validated and sanitised, PDO is just another line of defense. It doesn't cover the territory of a multitude of other input validation vulnerabilities like cross site scripting, but it does do a good job of protecting applications against SQL injection. The best strategy is only allowing known good data by whitelisting characters and matching input data against regular expression patterns, then using prepared statements to catch anything SQL injection-wise that the input validation misses, all in conjunction with a web application firewall like ModSecurity.

PDO has been built in to PHP since version 5.1.0, which was released in Nov 2005. Unless you've got a good reason for not using it in your PHP apps, you should be - it is a portable replacement for the old mysql_* functions and other platform-specific functions with the added benefit of protection against SQL injection.

[1] http://dev.mysql.com...t-problems.html
[2] http://dev.mysql.com...-cache-how.html

src: http://ezinearticles...-PDO&id=1815110 (hoho! Blind SQLi?)

Prepared statements / parameterized queries are sufficient to prevent 1st order injection on that statement. If you use un-checked dynamic sql anywhere else in your application you are still vulnerable to 2nd order injection.

2nd order injection means data has been cycled through the database once before being included in a query, and is much harder to pull off. AFAIK, you almost never see real 2nd order attacks, as it usually easier to social-engineer your way in.

One way to accomplish a 2nd order injection attack is when a value stored in a database is then used as a literal in a query. This isn't the best example, because it could still be stopped by a prepared statement, but the concept still applies.

Let's say you put this value in a text box that's expecting a name (assuming MySQL DB for this question):

' + (SELECT UserName + '_' + Password FROM Users LIMIT 1) + '

A prepared statement will make sure that select query doesn't run at the time of insert, and store the value correctly in the database. But if later on the name is retrieved and then used a literal in another query you'll get to see someone else's password. And since the first few names in users table tend to be admins, you may have just given away the farm. (Also note: this is one more reason not to store passwords in plain text!)

Again, this particular example isn't that great, but I try not to spend too much time thinking up real attacks, and even if I did I wouldn't post it straight up in public.

src: http://stackoverflow...t-sql-injection


Still waiting for your expert point of view :)

#3 aelphaeis_mangarae

aelphaeis_mangarae

    Members

  • Sergeant Major
  • 973 posts

Posted 12 September 2010 - 05:32 AM

So, do you guys use prepared statements? Is it that effective for time/ressources ? Is it really secure ?


Yes prepared statement will prevent SQL Injection, however that doesn't mean they will prevent the insertion of unwanted data into database. SQL commands other than the prepared query will not be able to be executed.

Someone correct me if I am wrong, as I would very much like to know if there is a way, but I am sure there is no way of injecting SQL statements when using a prepared statement.


Maybe someone might figure out a technique sometime, but from what I know about prepared statements I think that is very unlikely. They appear to be an effective way of preventing SQL Injection.


Prepared statements standard in PHP6 ? .. Means goodbye to the majority of SQL Injection in PHP(6) applications, doesn't mean however everyone will use prepared statements (just like in other languages such as Java). Although most who don't I am sure will use [effective] input validation or some other measure to prevent SQL Injection.


What you have posted from stackoverflow is correct.





Also tagged with one or more of these keywords: php, sql