All the Perl that's Practical to Extract and Report

Apostrophe hell with SQL

djberg96 on 2003-01-30T19:00:31

Bonus points if you can figure out what might cause problems with this bit of sql: --Dan's minstrel wrote this sql select column1, column2 from sometable where column1 = ?

Any guesses?

Perl's DBI (or perhaps Oracle's DBD) parses this sql properly. However, TOAD, TORA and Ruby's DBI do not. The problem, believe it or not, is the apostrophe when combined with a placeholder (although TORA seems to choke even with a hard coded value). It seems everything east of the apostrophe (inlcuding the apostrophe) is getting lumped together with the "select" clause.

I can't speak for TOAD, but the problem in Ruby's DBI lies in the simple lexer it uses to parse sql when placeholders are used. Shouldn't be too hard to fix, though.

Only five hours of work down the tubes thanks to this bug. No biggie. GAH!

Update: Michael Neumann has fixed this for the next release. Hooray!

Do all DBD's handle comments?

dws on 2003-01-31T05:10:25

Are you trying to pass a comment through DBI? That's an iffy proposition. It's been my experience that database client tools do a bit of preprocessing before shipping SQL to the server, and part of that preprocessing is to strip comments.

Re:Do all DBD's handle comments?

djberg96 on 2003-01-31T22:47:36

I'm passing it to prepare(). The sql is stored in an external file, and I like to include comments so I remember what the point of the query was (in case I forget). Stripping the comments out is fine, but choking on them isn't. ;)

Re:Do all DBD's handle comments?

entropic on 2003-02-02T10:30:42

DBD::Pg knows how to handle comments w/ an "'" in the comments

there was a thread about rewriting DBD::Pg from scratch on the dbi list that discussed sql comments