Beefy Boxes and Bandwidth Generously Provided by pair Networks Joe
"be consistent"
 
PerlMonks  

Wrong SQL Syntax?

by Frederic_S (Novice)
on Dec 03, 2010 at 12:34 UTC ( #875162=perlquestion: print w/ replies, xml ) Need Help??
Frederic_S has asked for the wisdom of the Perl Monks concerning the following question:

Hi there!

I'm currently trying to customize Bugzilla. I now know where I need to add code in order to achieve the changes I need, but dbh seems to behaving a problem with an @ sign I'm using.

Here's the code:

my $user_login_name = 'name.surname@company.com'; my $sth = $dbh->prepare("SELECT userid, login_name, realname FROM prof +iles WHERE login_name=$user_login_name"); $sth->execute(); while(my @zeile_user = $sth->fetchrow_array()) { $user_id_custom = $zeile_user[0]; $login_name_custom = $zeile_user[1]; $realname_custom = $zeile_user[2]; }

The error I get is:

DBD::mysql::st execute failed: You have an error in your SQL syntax; c +heck the manual that corresponds to your MySQL server version for the + right syntax to use near '@company.com' at line 1 [for Statement "SE +LECT userid, login_name, realname FROM profiles WHERE login_name=name +.surname@company.com"] at C:/Bugzilla/buglist.cgi line 1079

I thought if I pass the login_name in '' it would work, but it's not. I'm sorry if this is a stupid question. I'm new to perl (coming from PHP) and this all very confusing for me :-(

Maybe someone could enlighten me a bit as to what I'm doing wrong?

Cheers

Fred

Comment on Wrong SQL Syntax?
Select or Download Code
Re: Wrong SQL Syntax?
by Corion (Pope) on Dec 03, 2010 at 12:43 UTC

    Why do you believe that Perl (or DBI) is telling you a lie?

    You have an error in your SQL syntax sounds very convincing to me. Is the string you pass to DBI valid SQL?

    Most likely, you want to read about DBI placeholders and use these instead of interpolating data directly into your SQL strings. See also http://bobby-tables.com.

Re: Wrong SQL Syntax?
by moritz (Cardinal) on Dec 03, 2010 at 12:44 UTC
    WHERE login_name=$user_login_name");
    That's your problem. It should be
    my $user_login_name = 'name.surname@company.com'; my $sth = $dbh->prepare("SELECT userid, login_name, realname FROM prof +iles WHERE login_name = ? "); $sth->execute($user_login_name);

    Please read up on placeholders in the DBI manual.

Re: Wrong SQL Syntax?
by kcott (Abbot) on Dec 03, 2010 at 12:48 UTC

    Change login_name=$user_login_name to login_name='$user_login_name'.

    -- Ken

        ... until somebody tries to enter a username that contains a '.

        No argument with that. The various responses indicating the use of placeholders (including your own) are a better general solution.

        My post was based on:

        • Frederic_S seemed to think that the quotes used in the assignment to $user_login_name would be propagated to the SELECT statement: "I thought if I pass the login_name in '' it would work ...".
        • The fact that what he was trying perhaps worked in a different language: "I'm new to perl (coming from PHP) ...".
        • The specific question asked: "... what I'm doing wrong?".
        • The code as presented which didn't indicate any user input.

        I might also point out that when I started to type up my response there where no current replies: I wasn't intending to provide a better solution or invalidate any answers that were submitted in the few minutes before my own.

        Update: Added a blockquote at the top.

        -- Ken

Re: Wrong SQL Syntax?
by McDarren (Abbot) on Dec 03, 2010 at 12:48 UTC
    Use Placeholders
    my $sth = $dbh->prepare("SELECT userid, login_name, realname FROM prof +iles WHERE login_name = ?"); # and then $sth->execute($user_login_name);
    Cheers,
    Darren
Re: Wrong SQL Syntax?
by marto (Chancellor) on Dec 03, 2010 at 12:49 UTC

    Looks like you've not tried this SQL within MySQL, it wouldn't work there either. You need to quote text/varchar values:

    SELECT userid, login_name, realname FROM profiles WHERE login_name='a@ +b.com';

    However, the sensible approach would be to use placeholders as described in the DBI documentation. See also Database Programming from the tutorials section.

Re: Wrong SQL Syntax?
by sundialsvc4 (Monsignor) on Dec 03, 2010 at 13:28 UTC

    Very briefly:   you insert not-quoted question marks into your SQL syntax.   Then, during the execute call, you provide a corresponding number of values.   There are three really good reasons to do this:

    1. You can prepare a query one time, then execute against that statement-handle as often as you like, substituting appropriate values each time.
    2. “SQL insertion” becomes impossible.   It no longer matters what the inserted parameter-text might contain, because the SQL parser never considers the inserted parameters as “possibly being SQL.”   They are not.   Period.
    3. Especially in high-volume situations, it is noticeably more efficient.   prepare can be a fairly expensive operation, which becomes an issue if you have to do it a few hundred thousand times.

Re: Wrong SQL Syntax?
by planetscape (Canon) on Dec 04, 2010 at 02:06 UTC

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (4)
As of 2014-04-19 02:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (475 votes), past polls