Problems connecting to server 2

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

Ignore other wisdom seeking, it was connecting to the server but then failing at the first statment here is an example:

use DBI();


$dbh = DBI->connect("dbi:ODBC:TLRS",'sa','') or die "$DBI::errstr\n";
****$sth=$dbh->prepare("SELECT * FROM users where user like'sparkie' a
+nd passwd like 'foo' ");
$sth->execute();
while($ref = $sth->fetchrow_hashref)
{
    print"$ref->{'user'},$ref->{'passwd'},$ref->{'allow'}\n";
}
$sth->finish();

$dbh->disconnect;
[download]

It seems to fail when it reaches ****. The Idea is to finally use a sort of password control to gain access to certain areas of the site. (I know the way that I am doing it is probably not very secure but I am just playing to try and get some ideas on how to use perl and databases) The one thing that I don't understand is why it was working earlier but not now.
p.s. Thanks to c-era on the different way of connecting.
Cheers
Sparkie

Comment on Problems connecting to server 2 Download Code

Replies are listed 'Best First'.
RE: Problems connecting to server 2 by c-era (Curate) on Nov 08, 2000 at 21:32 UTC
Try using a "USE" statement in your "SELECT" statement. This will make sure that you have the correct database. `$sth=$dbh->prepare("SELECT * FROM users WHERE user LIKE 'sparkie' AND +passwd LIKE 'foo' USE master");` [download] Another thing you may want to consider is using place holders in your prepares. This can speed up your code if you use similar statments. use DBI(); my $user1 = 'sparkie'; my $user2 = 'c-era'; my $pass1 = 'foo'; my $pass2 = 'bar'; $dbh = DBI->connect("dbi:ODBC:TLRS",'sa','') or die "$DBI::errstr\n"; $sth=$dbh->prepare("SELECT * FROM users WHERE user LIKE ? and passwd L +IKE ? USE master"); $sth->execute($user1, $pass1); while($ref = $sth->fetchrow_hashref) { print"$ref->{'user'},$ref->{'passwd'},$ref->{'allow'}\n"; } $sth->execute($user2, $pass2); while($ref = $sth->fetchrow_hashref) { print"$ref->{'user'},$ref->{'passwd'},$ref->{'allow'}\n"; } $sth->finish(); $dbh->disconnect; [download] You may also want to consider using "=" instead of "LIKE". Using "=" will increase your performance, but there are times that you will have to use "LIKE". I hope this helps.	[reply] [d/l] [select]
Re: Problems connecting to server 2 by PsychoSpunk (Hermit) on Nov 08, 2000 at 21:23 UTC
sparkie, here's a way to find out what the database is reporting when the $sth fails: `$dbh = DBI->connect("dbi:ODBC:TLRS",'sa','', {RaiseError =>1}) or die "$DBI::errstr\n";` ALL HAIL BRAK!!!	[reply] [d/l]
Re: Problems connecting to server 2 by Jonathan (Curate) on Nov 08, 2000 at 19:56 UTC
what does **** have to do with password control? If you want the user to provide a password why not ask them for it? You could then add the value to your connection parameters. If this is for a batch routine then hardcoding passswords is not unusual. `print "\tEnter you password: "; system "stty -echo"; $user_password = <>; system "stty echo"; chomp $user_password;` [download] I could lie down like a tired child, And weep away the life of care Which I have borne, and yet must bear. Shelley 1792-1822	[reply] [d/l]
Re: Problems connecting to server 2 by wardk (Deacon) on Nov 08, 2000 at 20:01 UTC
I noticed in your first post that you were creating the complete connect string, I would advise continuing this practice. By using a DSN, you cannot pick up the script and move it to another box without replicating the DSN. Also, if the local box is re-genned, re-installed, etc you risk losing the DSN. Also, really consider having the sa password changed from the default of no password. Create a user with relevent permissions and try to avoid doing things as sa. (Unless of course you are the admin and these scripts require sa usage). Everyone knows SQL Server defaults sa to null. This is a gaping security hole. Good luck, Perl/ODBC/MSSQL can get along quite nicely.	[reply]
Re: Problems connecting to server 2 by Sparkie (Initiate) on Nov 08, 2000 at 20:01 UTC
as I said in my post I am just playing it was the only idea that I could come up with for a use of a database. The way that I am doing it is through the web\cgi so where it says sparkie and foo that will be replaced with something like $FORM{'user'} and $FORM{'passwd'}. Cheers for the advice though I will use that method if/when I need to use a password.	[reply]