Wrong SQL Syntax?

[Frederic_S]

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 = '[e-mail]';

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
[e-mail]"] 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

[moritz]

WHERE login_name=$user_login_name");

That's your problem. It should be

my $user_login_name = '[e-mail]';

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.

[Corion]

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.

[McDarren]

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

[marto]

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.

[sundialsvc4]

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.

[planetscape]

See also: Detect SQL injection

[kcott]

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

[moritz]

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

[herveus]

Howdy!

Ah, yes. Little Bobby Tables.

[kcott]

... 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.