Hardening PHP: SQL injection - Complete walkthrough

Below are the slides from the presentation I recently gave on SQL injection on OWASP Poland Chapter meeting. The materials teach how to use prepared statements, how to escape and write secure stored procedures. Many PHP projects are covered - PDO, Propel, Doctrine, Zend Framework and MDB2. Multiple gotchas and caveats are included. I discuss why escaping is usually the wrong choice, which practices to avoid or follow and how stored procedures sometimes offer no protection at all.

I tried to make this as complete as possible, so a PHP developer could learn how to protect his applications no matter what framework / database he uses.

English version

SQL Injection: complete walkthrough (not only) for PHP developers

View more presentations from Krzysztof Kotowicz.

Polish version

Kompletny przewodnik po SQL injection dla developerów PHP (i nie tylko)

You could also watch video recorded from the presentation. There are already some comments on the slides on niebezpiecznik.pl (Polish), but of course feel free to add comment here.

You're from Cracow and want to beat SQL injection?

Anyone interested in secure development probably knows what OWASP is. If not - it's a worldwide non-profit organization focused on web application security.

There is an upcoming OWASP Poland chapter meeting in Cracow, Poland next week. This time it is focused on secure PHP application development.

I will be giving a presentation there - it's entitled
"SQL injection: Complete walktrough (not only) for PHP developers".

We will talk a bit about the theory and demonstrate an attack. Then I'll show how to write code immune to SQL injection in various PHP frameworks, using various databases. We'll also talk about writing secure stored procedures in Oracle, MS SQL Server and MySQL. Different gothchas, bugs and tricks will be covered, so even if you think you know the subject - it will surprise you.

The meeting is totally free and no registration is required^*, so if anyone wants to develop securely in PHP or deals with databases (who doesn't?), please bring your fellow developers and come. It's important - please come and let's beat the SQL injection once and for all! 

I'll share the floor with Łukasz Pilorz who will talk about creating Secure PHP framework - and, given some internal details, I can assure you there's much to expect from this talk - don't miss it!

Date: 10.03.2010 (Wednesday), 17:00
Place: Compendium Centrum Edukacyjne, ul. Tatarska 5, Kraków, I piętro

Update: Read the full announcement from OWASP mailing list.

^* If you're planning to go, please drop a mail to Przemysław from the link above to prepare the room for the audience.

Weird date format from FreeTDS with mssql.datetimeconvert = 0

Today I encountered a problem with date field format in MS SQL Server 2000. We are accessing the server through FreeTDS and for data abstraction we are using latest stable PEAR MDB2_Driver_mssql v 1.2.1.

We were migrating to newest daily FreeTDS. As soon as we recompiled FreeTDS, our PHP applications started acting weird, showing invalid dates. We narrowed down all problems to a change in date format we got from SQL server.

A simple query:

echo $mdb2->getOne("SELECT datefield FROM table");

now returned some weird data format:

2009-01-01 31695:06

However, when we used mssql_* functions, skipping MDB2 altogether the date format was OK. I noticed one bug report: mssql.datetimeconvert force set to 0 - generate wrong dates. It was correct - even though we used mssql.datetimeconvert = 1 in our php.ini, MDB2 driver sets it to 0 on connect().

But the same code used to work with older FreeTDS. It seems that FreeTDS changed its internal date format, some locale settings etc. And disabling datetimeconvert by MDB2 triggered the problem as we started getting this new weird format.

I didn't want to mess up with internal date handling by MDB2 and keep patches to apply on future MDB2 updates - luckily we found a simple solution: recompile PHP with the newest FreeTDS libraries and everything goes back to normal. The same script now outputs:

2009-09-29 18:11:00

Even though mssql.datetimeconvert = 0 thanks to MDB2, we are receiving standard Y-m-d H:i:s format we love so much. Hope that helps anyone with similar problem.