encoding to prevent sql injection in both perl and php

[mandog]

I'm working in both PHP and Perl. A PHP script takes various values from untrusted users and stores them in a sqlite database. Periodically, a perl script reads the sqlite database and inserts previously uninserted values into a mysql database on the other side of the internet.

I am limited in the chars I can deny to users.

Inserting into the mysql database, DBI placeholders are fine.

On the php side, sqlite_escape_string() seems to be the way to go.

Unfortunately, there does not seem to be a sqlite_udf_decode_binary() for DBI.

Right now my plan is to use base64_encode() in PHP before storing the values in sqlite and MIME::Base64 after pulling them out of the sqlite database with perl.

Better ideas ?

[jdtoronto]

Given that you seem to be talking about binary values, what are you storing?

The sqlite_escape_string() function probably does much the same as the quote method in DBI, which of course is really surplanted by using placeholders.

If you need true binary capability then MIME::Base64 is probably as good as anything. But it seems you really only want escaped values. Try writing the escaped version into SQLite and see what DBI reads back!

jdtoronto

[mandog]

I'm storing text, name, address, phone, etc, plus whatever null bytes and other naughtiness the world gives me. I do just need to escape stuff, but I need (want?) to do it in a consistant, documented, predictable way.

I am loath to just try it, as the effort of using MIME encoding is less than the effort of developing a validation suite. :->

[CountZero]

As you do not know how PHP has escaped or encoded the data, there is unfortunately no easy way to reverse the effects of the sqlite_escape_string function outside of PHP.

However, I really wonder if escaping the data when storing it in the sqlite database is a good thing. Rather than blindly accepting any odd data and relying on the sqlite_escape_string function to store it safely into the database, IMHO one should validate the data before storing it and rejecting data which has unacceptable content. Of course the fact that sqlite can store any type of data in any column whatever its declared data-type (some consider this a bug, other think it is a feature) allows you to be careless in storing data and there is really no need --it seems-- to encode/decode your data.

Of course since MySQL uses static typing for its columns you must take care of what you are storing, unless you always use BLOB-columns.

[herveus]

Howdy!

As I read mandog's description, I see the SQLite data store being used as a simple, asynchronous transfer mechanism for getting user input from the PHP to the Perl. Clearly, the Perl side needs to Do Things To The Data along the lines of untainting it (in effect), as the data in SQLite is unfiltered.

Base 64 encoding should be a portable scheme for encoding in PHP and decoding in Perl for closer examination. Once it is encoded, there will be only printable characters, and none of them will be single-quotes. Thus, running it through sqlite_escape_string will not do anything to the string.