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

I am having trouble loading an Access DB using Win32::ODBC. The code is loading a path and file name into a table and it fails when the path name contains an apostrophe: e..g, H:\DCIM\Paula's scrap book\P1.jpg.

If I remove the apostrophe, the field in the table is populated without error. Otherwise, I get the following in error: 

Database insert error insert_jpg Subroutine: [-3100] [1] [0] "[Microso
+ft][ODBC Microsoft Access Driver] Syntax error (missing operator) in 
+query expression ''H:\DCIM\Paula's scrap book\P1.jpg')'."

[download]

In the code example below, $j[0] = H:\DCIM\Paula's scrap book\P1.jpg

Can anyone tell me how to load this string without stripping out the apostrophe in Paula's?

Here is my code snippet: 

$SqlStr = "INSERT INTO tbl_jpgs (jpg_seq, suborder, filename) VALUES( 
+$rcd,$i,'$j[0]')";

sub insert_jpg
{
  my $SqlStr = shift;
  if ($objDC->Sql("$SqlStr"))
  {
    die "Database insert error insert_jpg Subroutine: " . $objDC-> Err
+or() . "\n";
  }
}

[download]

Janitored by Arunbear - added code tags, as per Monastery guidelines

Replies are listed 'Best First'.
Re: INSERT INTO problem with Win32::ODBC and MS Access
by bart (Canon) on Apr 19, 2005 at 07:04 UTC
    This clearly shows the trouble people run into when building SQL expressions out of raw user supplied data, without the use of placeholders and without the help of any provided escape/quote mechanisms —it looks to me like Win32::ODBC offers neither. Be glad that this showed up in a test, and not in production, or you'd have been very vulnerable to SQL injection attacks. Now, you just have a failed test.

    I can't help but wonder why people keep using Win32::ODBC. Can you explain? It's a crufty old module, in my view. Its interface is very primitive — C-like, if you wish, sprinkled with custom constants everywhere and with no abstraction at all. Take this of an example of how ugly it is (in my eyes):

    Transact ( TYPE ) *
    Forces the ODBC connection to perform a rollback or commit transaction.

    TYPE may be one of:

    • SQL_COMMIT
    • SQL_ROLLBACK

    NOTE: This only works with ODBC drivers that support transactions. Your driver supports it if true is returned from: 

        $O->GetFunctions($O->SQL_API_SQLTRANSACT)[1]

    [download]
    Those are numerical constants, BTW. They've been imprted, and I'm not sure there's even a runtime check if you used a proper value. And what's that with that arrayindex, 1?

    It doesn't do placeholders, quoting or escaping, the docs say:

    This module has been neither optimized for speed nor optimized for memory consumption.
    So it could use some work.

    But instead, this module hasn't been updated in over 8 years. That's right, that's the latest version on CPAN, 0.032, of which the source contains the statement: 

    $ODBCPackage::Version = 970208;

    [download]
    According to the Win32::ODBC FAQ, that is in yymmdd format: 8 February, 1997. BTW, this version number isn't even Y2K compatible.

    So what's the alternative? DBI, for a start, plus DBD::ODBC or DBD::ADO. It is actively maintained, supports placeholders, or quote if you do insist do build the SQL by hand.

    Personally, I like the DBIx::Simple layer on top of it, with built in SQL::Abstract support, so your code could be replaced with the line 

    $db->insert('tbl_jpgs', { jpg_seq => $rcd, suborder => $i, filename =>
+ $j[0] });

    [download]
    It takes care of building the SQL statement for you, using placeholders, and executes it using the data you supplied. So it would be safe from SQL injection attacks, it would just work with your problematic data.

    Not all is well, though: the table name isn't escaped/quoted, so improper choice of table name, as is so customary with MS Access, could easily make it barf. I blame SQL::Abstract.

[reply]
[d/l]
[select]
Re: INSERT INTO problem with Win32::ODBC and MS Access
by davidrw (Prior) on Apr 19, 2005 at 03:09 UTC
    Does escaping the single quote w/another quote work in MS-Access-land? If so, a regexp like: 
    $j0 =~ s/'/''/g;

    [download]
    Should do the trick.

    A couple other possibilities to investigate:
    * Does the $objDC support the binding of variables?
    * Can you use DBI instead (i'm not sure offhand how DBI/ODBC mesh)?
    If either of those are true, then the code becomes something like: 
    $SqlStr = "INSERT INTO tbl_jpgs (jpg_seq, suborder, filename) VALUES( 
+?, ?, ?)";
$dbh->do($SqlStr, {}, $rcd, $i, $j0);

    [download]
    And the quoting will be done automagically for you. One last note: please use the <code></code> tags for snippets.
[reply]
[d/l]
[select]
Re: INSERT INTO problem with Win32::ODBC and MS Access
by holli (Abbot) on Apr 19, 2005 at 06:34 UTC
[reply]

Back to Seekers of Perl Wisdom