Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Calling SQL with embedded quotes

by budman (Sexton)
on May 21, 2002 at 07:39 UTC ( #168055=perlquestion: print w/replies, xml ) Need Help??

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

Hi, I was skimming thru many articles, yet I couldn't find anything on this problem: I'm trying to update/insert into a Sybase database using the Sybperl module. The problem is one of the values that needs to be set may contain a single quote from time to time. For example, the value would be something like: RunSql "delete sometable where strike like '%A-Za-z%'" I've tried many different things, even escaping all non-characters. When the line is run, it croaks.
$test = q/RunSql "delete sometable where strike like '%[A-Za-z]%'"/; $test =~ s/(['%\[\]\\])/\\$1/g; $sql = "update job set cmd='". $test ."' where jobname='somejob'";
Thanks for any help. budman

Replies are listed 'Best First'.
Re: Calling SQL with embedded quotes
by virtualsue (Vicar) on May 21, 2002 at 08:40 UTC
    If you were using the DBI module to access the database, you could take advantage of the quote method, which takes care of database-specific quoting automagically.
    $test = $dbh->quote($test);
    I have never done any sybase work, but the syntax of your delete statement looks incorrect. Maybe that is what is causing the problem?
Re: Calling SQL with embedded quotes
by grep (Monsignor) on May 21, 2002 at 08:43 UTC
    If at all possible I would switch to DBI instead of sybperl. DBI is the standard for perl database access and gives you a (mostly) consistant interface to whatever database you want to access wether it be Sybase or PostGreSQL or MySQL.

    DBI and DBD::Sybase give you some nice methods for dealing with your quoting problem like placeholders and binding

    But back to your original question, in the sybperl docs it explains you have both the 'dbsafestr' method and placeholders available with the 'ct_dyn_prepare' method. My personal preference is for placeholders since they make for efficient queries if reused.

    Of course you can always go to the monastary's mpeppler's page and read about both.

    grep
    These are not the monks you are looking for, move along

      I have to second use DBI instead. Also, you should get in the habit of using placeholders and binding, as they can make a huge difference on performance.

      Where I'm working now, most of our old code didn't use placeholders and binding. Now that we are literally doing 1000X more transactions then when the program was written, we are feeling the pain. We've gone back and started to change all of our old code, and it has made a huge difference. Not only are we able to keep up now, but the program now responds noticeably faster then it did before. I see using placeholders and binding like using strict and warnings, you may not think you need to use them, but if you don't use them, it will come back to haunt you.

      PS We use an Oracle database.

      PPS We are doing this to both our perl and java code, and both languages are benifiting from using placeholders.

        Note that while usually usage of placeholders is standart way to deal with such problems and it is highly recomended with most databases in case of Sybase implementation of placeholders have a number of different problems. It is covered in POD documentation for DBD::Sybase in chapter 'Using ? Placeholders & bind parameters to $sth->execute'.

        --
        Ilya Martynov (http://martynov.org/)

Re: Calling SQL with embedded quotes
by mpeppler (Vicar) on May 21, 2002 at 16:04 UTC
    Your sample above should work with the following:
    $test = q/RunSql "delete sometable where strike like '%[A-Za-z]%'"/; $test =~ s/'/''/g; $sql = "update job set cmd='$test' where jobname='somejob'";
    You use '' to quote the string that you set "cmd" to, so you just need to make sure that any ' quotes inside that string are doubled.

    There are other methods as well - placeholders for example when using DBI or Sybase::CTlib as mentioned by others in this thread, but there are drawbacks to those as well - nothing's perfect :-)

    Michael

      Thanks all for the advice on placeholders and the use of DBI, both I will further investigate and learn. :)

      The delete does work as indicated, but that delete statement is from a field already in an existing database. I'm just trying to read from one table, and store the field in another table. When I try to store the data, I always get an syntax error due to the inside single quotes. Sometimes there are backticks as well. I guess the real question was how do I stop Perl from interperting the command line as just text and not code that should be executed.

      I'll try Michael's suggestion to see if that works for me.

      Thanks again.
      budman
        That was quick...

        Just doubling the single quotes did the job!

        Thanks.
        budman
Re: Calling SQL with embedded quotes
by davis (Vicar) on May 21, 2002 at 08:34 UTC
    Should "delete sometable" be "delete from sometable"?
    cheers.
    davis
    Is this going out live?
    No, Homer, very few cartoons are broadcast live - it's a terrible strain on the animator's wrist

      No. In Sybase Transact-SQL, the from keyword in a delete statement is optional.


      Everything went worng, just as foreseen.

        The reason the FROM is optional is that you can do the following in Transact-SQL:
        delete mytable from mytable m, theirtable t where m.somevalule = t.somevalue and ...
        The same syntax can be used for UPDATE. Very nice for doing set based operations.

        Michael

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://168055]
Approved by virtualsue
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (4)
As of 2021-02-25 08:35 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?