Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

perl db query problem 2

Status
Not open for further replies.

alx59

Technical User
Joined
May 27, 2001
Messages
2
Location
FR
Hi
i'm new to Perl so sorry if my question sounds stupid :)

i try to query my Mysql database using:

$test=&quot;<script>document.write(top.identif);</script>&quot;;
$sth = $dbh->prepare(q{SELECT * FROM comptes WHERE Login=$test});
$sth->execute();

but it doesnt work
where is the problem? cause i already spent hours for nothing :(
thanx



 
I'd need to double-check this (I'm sorry I'm not able to double-check at the moment- it's 2:45AM and I'm on my way to bed), but I hope I'll be able to point you in the right direction.<br><br>$sth = $dbh-&gt;prepare(&quot;SELECT * FROM comptes WHERE Login = $dbh-&gt;quote($test);&quot;);<br>$sth-&gt;execute;<br><br>I'm not sure where you got the q{ } format. Also, you might be wondering about the $dbh-&gt;quote() part. 'quote' is great for placing the proper quotation marks around strings (in your example with MySQL, <b>'</b> would work). You could duplicate this by doing the following:<br><br>$sth = $dbh-&gt;prepare(&quot;SELECT * FROM comptes WHERE Login = \'$test\';&quot;);<br><br>but you DON'T WANT TO DO THAT for the following reason- if you're using your program with a web interface and there is a chance that the value of $test is in ANY WAY tied to a user's input, somebody can do malicious things to your database. For example, imagine if $test had the following value:<br><br>$test = &quot;\'; DROP dbname\'&quot;;<br><br>Somebody can just delete your database if they know the name of it.. or they could do other harmful things, like change your password, give themselves access, etc. So use quote(), or there are other ways of doing the same thing.<br><br>I'd urge you to pick up the following book if you plan on using Perl's DBI Module: &quot;Programming the Perl DBI&quot;, by O'Reilly Publishers. I have found it indispensible. You might also want to pick up O'Reilly's MySQL/mSQL book (you can tell I'm an O'Reilly nut). <p>Liam Morley<br><A HREF="mailto:"></A><br>&quot;light the deep, and bring silence to the world.<br>light the world, and bring depth to the silence.&quot;
 
Thank you very much for your advices
I really appreciate your help
:)
Alex
 
no problem :o) <p>Liam Morley<br><A HREF="mailto:"></A><br>&quot;light the deep, and bring silence to the world.<br>light the world, and bring depth to the silence.&quot;
 
There is a much easier way to get values into a query, and the DBI will handle quoting them properly for you. Try this:
Code:
$sth = $dbh->prepare(q{SELECT * FROM comptes WHERE Login=?});
 $sth->execute($test);
You can put multiple ? in the query and multiple variables in the execute, it will take the variables in order and replace the ? in order, quoting them as needed.
Tracy Dryden
tracy@bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.
 
I'm not sure why I decided not to mention that :o) but thanks for pointing that out. <p>Liam Morley<br><A HREF="mailto:"></A><br>&quot;light the deep, and bring silence to the world.<br>light the world, and bring depth to the silence.&quot;
 
Also, if you really need to use a variable in the query itself, the &quot;generic quotes&quot; operator qq can be very useful. For example:
Code:
$sth = $dbh->prepare(qq[SELECT * FROM comptes WHERE Login='$test']);
$sth->execute();
I ALWAYS use the qq[...] form when I'm building a query. That way I can use variables if I need to, and don't have to worry about which kind of quotes I use inside the query (and it very unlikely you'll use square brackets in a query). Being able to use a variable in the query is especially useful when you may want to change the sort sequence to the user's preference. For example:
Code:
$seq = 'lastname,firstname'; # default sort
$seq = 'userid' if $F_seq eq 'byid'; # userid sort selected
$sth = $dbh->prepare(qq[select * from users order by $seq]);
$sth->execute();
Tracy Dryden
tracy@bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.
 
Tracy Dryden make a *VERY* good point about using question mark placeholders for values used in queries - it is good to use these for a number of reasons - placeholders

1. let perl dbi take care of quoting the values of
variables used in SQL queries. This is a *BIG* help,
since proper quoting is hard to do right in all
situations, and failure to do it right can cost you a
LOT of time in debugging.

2. give you greatly improved performance in situations
where you will use the same SQL over and over
again in a script where only the values of the
placeholder variables will change. For example,
a situation where you can prepare the SQL once
and then execute that SQL multiple times, each time
with different variable values - this allows the
database to parse the sql only the first time - from
that point on, the database uses the already-parsed
query.

I use placeholders in all my Perl DBI queries - the quoting issue alone makes it worth it for me.

As someone already pointed out, the book &quot;Programming the Perl DBI&quot; is an excellent book that I would also highly recommend.

HTH.
Hardy Merrill
Mission Critical Linux, Inc.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top