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

Hi im basically using cgi and DBI to create a few script. But early on I have found that im having problems with sql queries that use variables that are uninitialized at compile but become initialized at runtime. Below is a small sample of code: 
#!/usr/bin/perl -w

use CGI;
use strict;
use DBI;

my $query = new CGI->new();



print $query->header("text/html"),


$query->start_html(-title => "Database Test"),

$query->h1("Database Test"),

my $user = $query->param("username"),

my  $db_handle = DBI->connect("dbi:mysql:database=test;host=localhost;
+user=root;")
  or die "Couldn't connect to database: $DBI::errstr\n";
   
my $sql = "SELECT * FROM users WHERE id = '$user'";

my $statement = $db_handle->prepare($sql)
        or die "Couldn't prepare query '$sql': $DBI::errstr\n";

$statement->execute()
        or die "Couldn't execute query '$sql': $DBI::errstr\n";
    
while (my $row_ref = $statement->fetchrow_hashref())
{
   
    
    print "<b>$row_ref->{email}</b>" ;
    
}

$db_handle->disconnect();

 print "<h1>Database Test</h1>";

    $query->end_html;

[download]
The output i get is 
Database Test
(Username i enter in the form)DBI::db=HASH(0x838242c)
Database Test

[download]
if i change: 
my $sql = "SELECT * FROM users WHERE id = '$user'";

[download]
to 
my $sql = "SELECT * FROM users WHERE id = 1";

[download]
then I will get 
Database Test
(Correct email address)
Database Test

[download]
the error i get when i run it is 
Use of uninitialized value in concatenation (.) or string at login.pl 
+line 27.

[download]
line 27 being the sql statement. What can I do to use form data in my sql queries @_@ any help is much appreciated.

Replies are listed 'Best First'.
Re: Dealing with uninitialized values
by grep (Monsignor) on Oct 14, 2006 at 01:25 UTC
    First off use placeholders. Especially when getting data from CGI. Else you are wide open to a SQL Injection attack. It also handles quoting correctly. 
    # Put question mark here for your placeholder
my $sql = "SELECT * FROM users WHERE id = ?";

my $statement = $db_handle->prepare($sql)
        or die "Couldn't prepare query '$sql': $DBI::errstr\n";

# Put your $scalar here to be interpolated 
$statement->execute($user)
        or die "Couldn't execute query '$sql': $DBI::errstr\n";

    [download]
    As to your error - Have you checked the DB to see what's really in there. Your retrieval and deref look fine. So I'm guessing your data says 'UsernameDBI::db=HASH(0x838242c)' and you forgot to deref something when you inserted the data.


    grep
    One dead unjugged rabbit fish later
[reply]
[d/l]

      grep, the "DBI::db=HASH(0x838242c)" I think is the result of DBI->connect("dbi:mysql:database=test;host=localhost;user=root;") being printed.

      shalomgod, I would change the code like this to fix your error:
      my $user = '1'; #or whatever default value you want

print $query->header("text/html"),


$query->start_html(-title => "Database Test"),

$query->h1("Database Test"),

$user = $query->param("username"),

my  $db_handle = DBI->connect("dbi:mysql:database=test;host=localhost;
+user=root;")
  or die "Couldn't connect to database: $DBI::errstr\n";

      [download]
      I would also definitely recommend using placeholders, like grep said, and taint checking as well. I'd recommend checking out Ovid's CGI course at http://users.easystreet.com/ovid/cgi_course/, most importantly Lesson 3 on security.
[reply]
[d/l]
[select]
Re: Dealing with uninitialized values
by ikegami (Patriarch) on Oct 14, 2006 at 07:34 UTC 
    my $sql = "SELECT * FROM users WHERE id = '$user'";

    [download]

    is compiled as 

    my $sql = "SELECT * FROM users WHERE id = '" . $user . "'";

    [download]

    thus the concatenation error. The only thing there that could be undef is $user. The problem has nothing to do with the database, and all with an invalid web request ( that comma after $query->param("username")! ++chorny ).

    grep is right about using placeholders, though. Using unescaped data in an SQL statement (or anywhere else) is buggy and unsafe. Use placeholders.

[reply]
[d/l]
[select]
Re: Dealing with uninitialized values
by chorny (Scribe) on Oct 14, 2006 at 07:38 UTC
    I see several problems in your script. This string end with ',': 
    my $user = $query->param("username"),

    [download]
    I propose to rewrite it to 
    my $user = $query->param("username") || '';
if ($user) {
...all remaining code...
} else {
 print "No user<br>";
}

    [download]
    Also use placeholders, or $db_handle->quote. Placeholders are very convinient.
[reply]
[d/l]
[select]
Re: Dealing with uninitialized values
by fenLisesi (Priest) on Oct 14, 2006 at 08:03 UTC
    (Not directly related to your question) Using CGI and DBI without Taint is asking for trouble. You are taking $user out of the environment and stuffing it straight into your database query, without so much as a sanity check or placeholders.
[reply]
[d/l]
Re: Dealing with uninitialized values
by chromatic (Archbishop) on Oct 14, 2006 at 07:34 UTC

    How are you running it? If you run it the first time, of course there will be nothing in the username parameter, and you'll get errors.

    By the way:

    my $query = new CGI->new();

    ... you can safely drop the first new there.

[reply]
[d/l]
[select]
Re: Dealing with uninitialized values
by gam3 (Curate) on Oct 15, 2006 at 09:48 UTC
    Try 
    my $sql = "SELECT * FROM users WHERE id = ?";

my $statement = $db_handle->prepare($sql)
        or die "Couldn't prepare query '$sql': $DBI::errstr\n";

$statement->execute($user)
        or die "Couldn't execute query '$sql': $DBI::errstr\n";

    [download]
    If you really want to have $user in a string then you can add 
    $user ||= '';

    [download]
    In your code before you use it.
    -- gam3
    A picture is worth a thousand words, but takes 200K.
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom