Thread

1. escape single quote in INSERT command

   Hunter <dave_h4@yahoo.com> — 2002-11-22T13:00:01Z

   Hi Group - 
   
   I have a perl application for a registration form. I'd like to put
   escape characters in my insert command to accommodate for '
   (i.e. O'Brien, O'Malley, etc). I've tired double quotes, single
   quotes, back tick, forward ticks, curly bracket, round brackets - no
   success.
   
   
   Thanks, dave
   
   

2. Re: escape single quote in INSERT command

   Jeff Boes <jboes@nexcerpt.com> — 2002-11-22T18:38:17Z

   On Fri, 22 Nov 2002 08:00:01 -0500, Hunter wrote:
   
   
   > I have a perl application for a registration form. I'd like to put
   > escape characters in my insert command to accommodate for ' (i.e.
   > O'Brien, O'Malley, etc). I've tired double quotes, single quotes, back
   > tick, forward ticks, curly bracket, round brackets - no success.
   
   Are you using the DBI interface?  That's the most straightforward way to
   accomplish this.  Something like this:
   
   use strict;
   use DBI;
   my $dbh = DBI->new(...see docs for details...);
   $dbh->do(q|INSERT INTO mytable (col1) VALUES (?)|, undef,
     q|O'Malley|) or die $DBI::errstr;
   $dbh->commit;
   
   By using the '?' placeholder format for statements, you can pass in any
   data without having to worry about quoting.
   
   -- 
   Jeff Boes                                      vox 616.226.9550 ext 24
   Database Engineer                                     fax 616.349.9076
   Nexcerpt, Inc.                                 http://www.nexcerpt.com
              ...Nexcerpt... Extend your Expertise
   
   

3. Re: escape single quote in INSERT command

   mark carew <markcarew@bigpond.com> — 2002-11-22T21:50:35Z

   Hi Hunter,
   
       From my xbase++ program suite, sqlcontext class.
                   *
                   cValue := strtran(cValue,['],[\'])
                    *
       Its called masquarading, simply replace the single quote with back_slash
   + single quote.
   
       Regards
       Mark Carew
       Brisbane Australia
   
   
   
   
   
   

4. Re: escape single quote in INSERT command

   mark carew <markcarew@bigpond.com> — 2002-11-22T21:52:57Z

   Woops should have been  masquerading
   
   
   
   

5. Re: escape single quote in INSERT command

   Thomas Good <tomg@q8.nrnet.org> — 2002-11-26T18:23:53Z

   On Wed, 27 Nov 2002 mallah@trade-india.com wrote:
   
   > Why dont' you use prepare and execute  in case you are using DBI
   > same program is like this.
   >
   > $dbh = DBI -> connect ( "......");
   > $sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
   > $sth -> execute($a , $b );
   > $sth -> finish();
   > $dbh -> commit();
   > $dbh -> disconnect();
   
   > I'd like to put escape characters in my
   > > insert command to accommodate for '
   
   $dbh->quote() will do the escaping for DBI but be careful with dates
   as the variable binding does not always behave as expected.
   
   You can esc the single with another single, ala ANSI SQL: ''
   This works in Oracle, PG and MySQL for sure.
   
   In perl:  $name =~ s/\'/\'\'/g;
   $query = qq |insert into x values ('$name')|;
   and so on...
   
   Now, can some kind soul tell me how to do an 'insert into x select y;'
   where x is a numeric(19,2) and y is a money type???
   -----------------------------------------------------------------------
   Thomas Good                                  e-mail: tomg@sqlclinic.net
   Programmer/Analyst                           phone:   (+1) 718.818.5528
   Residential Services                         fax:     (+1) 718.818.5056
   Behavioral Health Services, SVCMC-NY         mobile:  (+1) 917.282.7359
   
       --            Geistiges Eigentum ist Diebstahl!              --
   
   
   

6. Re: escape single quote in INSERT command

   Rajesh Kumar Mallah <mallah@trade-india.com> — 2002-11-26T18:31:26Z

   > Hi Group -
   >
   > I have a perl application for a registration form.
   
   Same Here,
   
   Why dont' you use prepare and execute  in case you are using DBI
   same program is like this.
   
   $dbh = DBI -> connect ( "......");
   $sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
   $sth -> execute($a , $b );
   $sth -> finish();
   $dbh -> commit();
   $dbh -> disconnect();
   
   
   regds
   mallah.
   
   
   I'd like to put escape characters in my
   > insert command to accommodate for '
   > (i.e. O'Brien, O'Malley, etc). I've tired double quotes, single
   > quotes, back tick, forward ticks, curly bracket, round brackets - no success.
   >
   >
   > Thanks, dave
   >
   > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you
   > searched our list archives?
   >
   > http://archives.postgresql.org
   
   
   
   -----------------------------------------
   Get your free web based email at trade-india.com.
      "India's Leading B2B eMarketplace.!"
   http://www.trade-india.com/
   
   
   
   

7. Re: escape single quote in INSERT command

   Dan Langille <dan@langille.org> — 2002-11-26T18:47:20Z

   On 27 Nov 2002 at 0:01, mallah@trade-india.com wrote:
   
   > > Hi Group -
   > >
   > > I have a perl application for a registration form.
   > 
   > Same Here,
   > 
   > Why dont' you use prepare and execute  in case you are using DBI
   > same program is like this.
   > 
   > $dbh = DBI -> connect ( "......");
   > $sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
   > $sth -> execute($a , $b );
   > $sth -> finish();
   > $dbh -> commit();
   > $dbh -> disconnect();
   
   IIRC, there is a dbi->quote() function as well.  That should properly 
   escape anything.
   -- 
   Dan Langille : http://www.langille.org/