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.

In reply to Re: INSERT INTO problem with Win32::ODBC and MS Access by bart
in thread INSERT INTO problem with Win32::ODBC and MS Access by hcbsez

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.