[nzlug] quoting perl strings

[Robin Sheat]

On Monday 01 October 2007 10:52:27 Glenn Enright wrote:
> More specifically, Im assigning a value to a quoted string for an SQL
> statement. But one of the values has a single quote mark in it, and
> possible other unusual characters as well. whats the best thing (TM)
> to do here? Run a regex on it?
You should do it this way. There are three reasons for this: one is that it's 
slower. The better way I'll get into lets the DB cache compiled queries. The 
second reason is that if your data is coming from an untrusted source, the 
hostile attacker person can do SQL injection. The third reason is that bad 
characters won't cause it issues.

The better way is to use prepared statements. Details start here: 
http://search.cpan.org/~timb/DBI/DBI.pm#prepare

However, if you're doing something like taking a text file and throwing it 
into a database as a once-off operation, this is a bit of overkill. In that 
case, turn ' into \' and probably " into \" and it should do the trick. If 
you're in Perl, apply something like tr/\"\'/\\\"\\\'/ to it to convert 
(untested - and I always get my \s wrong).

-- 
Robin <robin at kallisti.net.nz> JabberID: <eythian at jabber.kallisti.net.nz>

Hostes alienigeni me abduxerunt. Qui annus est?

PGP Key 0xA99CEB6D = 5957 6D23 8B16 EFAB FEF8  7175 14D3 6485 A99C EB6D
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
Url : http://www.linux.net.nz/pipermail/nzlug/attachments/20071001/de6c00c2/attachment.pgp