mysql syntax problem

Hi all

when i query a mysql db within a perl script
this works fine...
$sth = $dbh->prepare ("SELECT venue from base1 WHERE op = 'K Trevan'");

this doesn't..
$sth = $dbh->prepare ("SELECT venue from base1 WHERE op = 'K O'Trevan'");

i know the issue is 'K O'Trevan'  in that the 2nd ' completes

i just don't know how to resolve it  - other than  changing 'K O'Trevan' to
'K O Trevan'

Any ideas appreciated

Steve










0
Steven_Massey
10/2/2002 8:06:16 AM
perl.beginners 29352 articles. 3 followers. Follow

4 Replies
234 Views

Similar Articles

[PageSpeed] 51

On Wed, Oct 02, 2002 at 09:06:16AM +0100, Steven_Massey@notes.amdahl.com wrote:
> when i query a mysql db within a perl script
> this works fine...
> $sth = $dbh->prepare ("SELECT venue from base1 WHERE op = 'K Trevan'");
> 
> this doesn't..
> $sth = $dbh->prepare ("SELECT venue from base1 WHERE op = 'K O'Trevan'");
> 
> i know the issue is 'K O'Trevan'  in that the 2nd ' completes
> 
> i just don't know how to resolve it  - other than  changing 'K O'Trevan' to
> 'K O Trevan'

Use placeholders:

    $sth = $dbh->prepare("SELECT venue FROM base1 WHERE op = ?");
    $sth->execute("K O'Trevan");

This is a little silly with literal values, as you can easily escape them
manually.  It works much better with data you get from sources that won't
escape it, such as user input.

Read about placeholders in perldoc DBI.

As for manually escaping your values, that's an SQL question, and you should
read the documentation on your database for information on that.


Michael
--
Administrator                      www.shoebox.net
Programmer, System Administrator   www.gallanttech.com
--
0
michael
10/2/2002 8:33:45 AM
Michael           Excellent - read DBI/placeholder

thanks



                                                                                                           
                      Michael Fowler                                                                       
                      <michael@shoebox         To:      Steven_Massey@notes.amdahl.com                     
                      .net>                    cc:      beginners@perl.org                                 
                                               Subject: Re: mysql syntax problem                           
                      10/02/02 09:33                                                                       
                      AM                                                                                   
                                                                                                           
                                                                                                           




On Wed, Oct 02, 2002 at 09:06:16AM +0100, Steven_Massey@notes.amdahl.com
wrote:
> when i query a mysql db within a perl script
> this works fine...
> $sth = $dbh->prepare ("SELECT venue from base1 WHERE op = 'K Trevan'");
>
> this doesn't..
> $sth = $dbh->prepare ("SELECT venue from base1 WHERE op = 'K O'Trevan'");
>
> i know the issue is 'K O'Trevan'  in that the 2nd ' completes
>
> i just don't know how to resolve it  - other than  changing 'K O'Trevan'
to
> 'K O Trevan'

Use placeholders:

    $sth = $dbh->prepare("SELECT venue FROM base1 WHERE op = ?");
    $sth->execute("K O'Trevan");

This is a little silly with literal values, as you can easily escape them
manually.  It works much better with data you get from sources that won't
escape it, such as user input.

Read about placeholders in perldoc DBI.

As for manually escaping your values, that's an SQL question, and you
should
read the documentation on your database for information on that.


Michael
--
Administrator                      www.shoebox.net
Programmer, System Administrator   www.gallanttech.com
--





0
Steven_Massey
10/2/2002 10:19:46 AM
>Use placeholders:

Could you also use quote() for this?

"Ain't no blood in my body, it's liquid soul in my veins"
~Roots Manuva

0
nkuipers
10/2/2002 4:25:43 PM
On Wed, Oct 02, 2002 at 09:25:43AM -0700, nkuipers wrote:
> Could you also use quote() for this?

Yes, $dbh->quote() can also be used for quoting strings.  I generally don't
suggest it because it's more awkward and makes for less readable code than
placeholders.


Michael
--
Administrator                      www.shoebox.net
Programmer, System Administrator   www.gallanttech.com
--
0
michael
10/2/2002 8:53:01 PM
Reply: