DBI mysql substring_index syntax error

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

This statement works fine from the mysql prompt:

  SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(path, "\\", -2), "\\", 1) AS dir FROM files

But I get a "DBD::mysql::st execute failed" error when I run this:

my $query = qq~
  SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(path, "\\", -2), "\\
+", 1) AS dir FROM files
~;
my $sth = $dbh->prepare($query);
my $rv = $sth->execute;
[download]

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\", 1) AS dir

So, I believe that something is not escaped properly in my statement and I have tried several different ways but no luck.

Any ideas?

Comment on DBI mysql substring_index syntax error Download Code

Replies are listed 'Best First'.
Re: DBI mysql substring_index syntax error by Loops (Curate) on Oct 22, 2014 at 04:16 UTC
If you print the string out you'll see it has been modified and no longer matches what you're using in MySql, instead: `my $query = 'SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(path, "\\ +\\", -2), "\\\\", 1) AS dir FROM files';` [download]	[reply] [d/l]
Re^2: DBI mysql substring_index syntax error by Rodster001 (Pilgrim) on Oct 22, 2014 at 04:19 UTC
Got it, changed to: SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(i.path, '\\\\', -2) , '\\\\', 1) AS dir Thanks!	[reply]
Re: DBI mysql substring_index syntax error by FloydATC (Deacon) on Oct 22, 2014 at 04:47 UTC
Use placeholders whenever possible, use `->quote()` everywhere else. This would take care of all escaping issues for you, and make you invulnerable against SQL injection attacks. -- FloydATC Time flies when you don't know what you're doing	[reply] [d/l]
Re^2: DBI mysql substring_index syntax error by Rodster001 (Pilgrim) on Oct 22, 2014 at 06:32 UTC
But, there are no variables in this query :) I agree with you, I always use placeholders.	[reply]
Re^3: DBI mysql substring_index syntax error by Laurent_R (Canon) on Oct 22, 2014 at 06:57 UTC
Yes, which means that you don't need interpolation and that you could use a differfent quoting mechanism. I can't test it, but using `q` instead of `qq` with your original string should probably be sufficient. Or you could possibly use the `quotemeta` function to get the escaping right.	[reply] [d/l] [select]