SQL and apostrophe 
Author Message
 SQL and apostrophe

Hi to everybody,

I'm using the Perl module Win32::ODBC on an NT 4 server to update an MS
Access 97 database from Web. When one field of the record inserted
contains an apostrophe I get an "SQL failed" Error, so I should find a
method to get harmless this apostrophe and  to insert it in the database
all the same.
Any suggestion?
Thank you for your attention.




Fri, 22 Dec 2000 03:00:00 GMT  
 SQL and apostrophe


Quote:
>I'm using the Perl module Win32::ODBC on an NT 4 server to update an MS
>Access 97 database from Web. When one field of the record inserted
>contains an apostrophe I get an "SQL failed" Error, so I should find a
>method to get harmless this apostrophe and  to insert it in the database
>all the same.
>Any suggestion?

escape special characters.  perhaps Win32::ODBC already has a method
to help yuo with this.

good luck :)

--

CGI Meta FAQ <URL:http://computerdog.com/CGI_MetaFAQ.html>
Comprehensive Perl Archive Network (CPAN) <URL:http://www.perl.com>
Perl Mongers Travel Deals! <URL:http://www.pm.org/travel.html>



Fri, 22 Dec 2000 03:00:00 GMT  
 SQL and apostrophe

Quote:

> Hi to everybody,

> I'm using the Perl module Win32::ODBC on an NT 4 server to update an MS
> Access 97 database from Web. When one field of the record inserted
> contains an apostrophe I get an "SQL failed" Error, so I should find a
> method to get harmless this apostrophe and  to insert it in the database
> all the same.
> Any suggestion?
> Thank you for your attention.



You have to double your apostrophes.

Either do something like

        $var =~ s/'/''/gm;
        $db->Sql("SELECT * FROM Table WHERE Name = '$var'");

or

        use Interpolation "'" => sub {$_=$_[0]; s/'/''/;"'".$_};
         # http://www.plover.com/~mjd/perl/Interpolation/
        $db->Sql("SELECT * FROM Table WHERE Name = $'{$var}'");

The second solution leaves your variables intact!

HTH, Jenda



Sat, 23 Dec 2000 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Recommendations for escaping apostrophes in DBI SQL statements?

2. Triple escape apostrophes

3. newlines and query -> param / problem with apostrophes

4. apostrophe character in text boxes and mysql

5. Apostrophes and ODBC

6. Help: How to pass thru apostrophe

7. ODBC and Apostrophe

8. DBI and apostrophes

9. Easy pattern matching for an apostrophe

10. Word\Line boundaries and apostrophes etc

11. Beta: SQL::Builder -- an OO SQL builder Perl module

12. ANN: SQL::Catalog - test, label, and retrieve SQL queries

 

 
Powered by phpBB® Forum Software