Please Help (checking SQL table...) & question

Dev Shed Forums Sponsor:

March 20th, 2001, 03:14 PM

Stenyj

Junior Member

Join Date: Mar 2001

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

I posted a problem I was having a few days ago.

I'm writting a login script, and want it to test first if the login and PW exists in a mySQL table, and do two different things depending on whether it does or doesn't exist (using an if statement - either granting the individual access, or telling them they don't have access).

I started with the following (as posted a few days ago):
------------------------------------------------------

#!/usr/bin/perl

use CGI;
use DBI;

$foo = new CGI;

$database = "*database name*";
$dbuser = "*my user name*";
$dbpassword = "*my password*";

$dbh = DBI->connect("DBI:mysql:$database", $dbuser, $dbpassword);

print $foo->header;

print "<HTML>\n";
print "<BODY BGCOLOR=WHITE TEXT=BLACK>\n";

$login = $foo->param('login');
$pw = $foo->param('password');

$statement = "SELECT user_access
FROM table_name
WHERE login = '$login' AND password = '$pw'";
$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
$sth->execute or die "Can't execute the query: $sth->errstr";
@row = $sth->fetchrow_array;
$user_access = $row[0];
$sth->finish;

# this is where I'd like to do an "if" statement, to see if # that exists. IF the account exists, it will have pulled # out an access level for that account. IF it doesn't
# exist, the script just stops responding because I guess
# it gets an error when trying to look for something that
# doesn't exist.

if ($user_access) {
blah blah blah
}
else {
blah blah blah
}
------------------------------------------------------

That obviously wasn't working (which was why I posted my question intialy).

dsb replied to my post suggesting I use the "rows function in DBI".

So I did the following at his suggestion:
------------------------------------------------------

#!/usr/bin/perl

use CGI;
use DBI;

$foo = new CGI;

print $foo->header;

print "Got here 1";

$login = $foo->param('login');
$password = $foo->param('password');

$database = "the_database";
$dbuser = "my_password";
$dbpassword = "my_password";

$dbh = DBI->connect("DBI:mysql:$database", $dbuser, $dbpassword);

print "Got here 2";

print "<HTML>\n";
print "<HEAD>\n";
print "<TITLE>Login Script</TITLE>\n";
print "</HEAD>\n";

print "<BODY BGCOLOR=BLACK TEXT=WHITE>\n";

print "Got here 3";

$sth->prepare("SELECT access FROM members WHERE login = ? AND password = ? ");
$sth->execute( $login, $password );
$rows = $sth->rows();

print "Got here 4";

if ($rows == 1) {
print "Welcome! You have access.\n";
}
else {
print "<FONT SIZE=+2 COLOR=RED>WARNING!</FONT><BR>You do NOT have access.\n";
}

print "Got here 5";

print "</BODY>\n";
print "</HTML>\n";
---------------------------------------------------------

MY PROBLEM:

As you can see I threw in some print statements to pinpoint any problems, etc.
The scripts runs up to the print "Got here 3", but seems to stop somewhere between the "Got here 3" and "Got here 4". I don't doubt it's at the execute statement.

Does anyone know why this is happening, or how I can fix it by chance?

I'm a self-taught programmer (as I'm sure anyone who programs regularly can see by my sloppy programming), but this is the first real "wall" I've run into...

Also, dsb mentioned that my query for DBIperl was written wrong.

Until he replied to my post previously, I would include the variables in the statement like the following:

$statement = "SELECT thread_name FROM $mb_id WHERE threadID = $thread";
$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
$sth->execute or die "Can't execute the query: $sth->errstr";

In this example $mb_id is a table I was using for a message board script, and $thread is the thread number of the message board...

Anyway, it seemed to be working fine. However if there is a better/proper way (as dsb mentioned) would anyone mind doing a few examples explaining how/why to do it that way?
He said I had to use "placeholders" (as I incorporated.. hopefully correctly... into the new version of my script - which still won't work :-( )

Anyway, I appreciate the time you've spent reading this.

Any feedback/help is MUCH apprecaited.

Thanx again,
Stenyj

March 20th, 2001, 04:04 PM

Stenyj

Junior Member

Join Date: Mar 2001

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

I got the script to work (was missing a couple of ' in the DQL statement line)

But am still interested in finding out if placeholders are necessary/better to use, rather then just including the variable in the statement.

Thanx,
Stenyj

March 26th, 2001, 11:29 PM

akashani

Junior Member

Join Date: Mar 2001

Posts: 0

Time spent in forums: < 1 sec
Reputation Power: 0

There are some benefits to using bind values (the syntax which uses the "?" placeholder), but some databases don't support it. You may in some situations gain a few precious milliseconds, but I think that's it. I'd check the docs to see if there is anything else.

Personally, I"ve been using DBI for 2 years now, and haven't once used bind values. I'd never even seen them before I saw your post. I guess you learn something new everyday.

- Amir

March 27th, 2001, 11:29 AM

w_lissmann

Junior Member

Join Date: Mar 2001

Location: Dallas, TX

Posts: 2

Time spent in forums: < 1 sec
Reputation Power: 0

RE: Please Help (checking SQL table...) & question

Hmm, let's see...

#!/usr/bin/perl

use CGI;
use DBI;

$foo = new CGI;

print $foo->header;
print "Got here 1";
.
.
.

$dbh = DBI->connect("DBI:mysql:$database", $dbuser, $dbpassword);
print "Got here 2";
.
.
.

First, your print "got here 1" statement should be after the <HTML> and <BODY> tags not before, otherwise it won't be printed out on the html document.

your dsn construct is missing the 'host' value, which is most probably why DBI is just dying and you're left to wonder what the hell is going on. DBI won't print error messages on to the html document, instead it'll write them to the error log. Try reading the perldocs on the CGI::Carp module if you want to redirect error messages on to the browser, I recently asked question about it on this forum, you can go read the reply it's still there. What I ususally do is run the script from the shell (you have to specify the paramenters in param=value pairs, if anything goes wrong you'll see whatever message DBI prints out.

Hope this helps.

March 27th, 2001, 01:27 PM

meeh82

Contributing User

Join Date: Sep 2000

Location: Riverside, CA

Posts: 63

Time spent in forums: < 1 sec
Reputation Power: 8

Your were close the first time

Here is your original code with some modifications:

#!/usr/bin/perl

use CGI;
use DBI;

$foo = new CGI;

$database = "*database name*";
$dbuser = "*my user name*";
$dbpassword = "*my password*";

$dbh = DBI->connect("DBI:mysql:$database", $dbuser, $dbpassword);

print $foo->header;

print "<HTML>\n";
print "<BODY BGCOLOR=WHITE TEXT=BLACK>\n";

$login = $foo->param('login');
$pw = $foo->param('password');

$statement = "SELECT user_access
FROM table_name
WHERE login = '$login' AND password = '$pw'";
$sql = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
$sth->execute($sql) or die "Can't execute the query: $sth->errstr";
@row = $sth->fetchrow_array;
$user_access = $row[0];
$sth->finish;

if ($user_access) {
blah blah blah
}
else {
blah blah blah
}

I am not really familiar with the way you structured your code, but this way is more simplified and it shows more steps.
We prepare the statement.
We execute the $sql statement.

Jonathan Donaghe

March 29th, 2001, 09:48 AM

JonLed

Contributing User

Join Date: Aug 2000

Location: Indiana

Posts: 614

Time spent in forums: 4 h 49 m 49 sec
Reputation Power: 10

BTW, those 'bind' values, as you called them, are parsed by DBI, not the database. It's the same thing as saying "select.... where blah = ".$dbh->quote($value)."...

The ? just allows you to fill the execute() method with values so you can define with query once and execute it multiple times (say, within a loop where redefing the query everytime would be wastful).

you can also do:
$dbh->do('select... where blah =?', $value);
where $value would fill in the 1st ? (and you can keep going on with that.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: