Perl + DBI + mysql - Escaping Strings

ayapejian has asked for the wisdom of the Perl Monks concerning the following question:

First post here ... having a bit of a problem, I know you can use the DBI function quote() to properly escape characters before using in a sql statement ... however in this case -full- sql statements are being passed (not just the content part) so I don't believe I can use 'quote()' ... is this correct? If I can't use 'quote()' in the normal fashion is their a function / module that performs the same function as 'quote()' without needing a db connection? I know 'quote()' changes the behavior based on the DB, but I just need something I can use that would escape for mysql ... if I can do this without a connection I might be able to get around the issue. Thanks

Comment on Perl + DBI + mysql - Escaping Strings

Replies are listed 'Best First'.
Re: Perl + DBI + mysql - Escaping Strings by graff (Chancellor) on Mar 05, 2008 at 03:46 UTC
If you are talking about constructing an sql statement in something like this manner: `my $table = "some_table"; my $columns = "foo,column2,another_col"; my $where_cond = "foo like '%bar%'"; $where_cond .= " and column2<4"; my $sql = "select $columns from $table where $where_cond";` [download] The "quote()" function (and the more sensible strategy of using "?" placeholders for values instead of quoted strings) cannot apply in this sort of construction. It only works in positions where SQL syntax would allow a quoted value to be used (i.e. in place of '%bar%' and "4" in the examples above).	[reply] [d/l]
Re: Perl + DBI + mysql - Escaping Strings by pc88mxer (Vicar) on Mar 05, 2008 at 03:49 UTC
The `quote` method assumes that its argument is a string literal. It can't go through an SQL statement, pick out the parts that look like string literals and then properly quote them. Is that what you're asking? Quoting is database driver specific. However, you probably can use the same algorithm for most of the commonly used databases. Have a look at the code for `sub quote` in the file `DBI.pm`. You'll see that there is some dependence on the database handle, but it is minimal. Please tell us more about what your circumstances are. If you are given an SQL statement, it should already be well-formed and any embedded string literals already properly quoted.	[reply] [d/l] [select]
Re: Perl + DBI + mysql - Escaping Strings by ikegami (Patriarch) on Mar 05, 2008 at 07:16 UTC
`$dbh->quote()` converts an arbitrary string into a SQL literals. If you already have SQL statements as you say, you shouldn't have any need for `quote`. If the problem is that you're trying to dynamically build those SQL statements in the first place, then you can look at the MySQL docs to see how it expects its string literals are quoted, or you can lift the code from DBD::MySQL (license permitting).	[reply] [d/l] [select]
Re: Perl + DBI + mysql - Escaping Strings by dragonchild (Archbishop) on Mar 05, 2008 at 03:19 UTC
Define the problem a bit more. Right now, it looks like you're having an XY problem. Plus, what happened when you tried it? My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]
Re: Perl + DBI + mysql - Escaping Strings by moritz (Cardinal) on Mar 05, 2008 at 07:35 UTC
Full SQL statements don't need to be quoted, because the string literals in them are already quoted. But I think you need to explain a bit more what you're trying to achieve.	[reply]