Problem with mysql query using DBI::Mysql

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

Hi monkers. Im facing a strange issue, i have done a piece of code to get connected to a db, select some values passed from a HTML form and use this value has a value of the query. MY db its on local and im using DBD::Mysql driver to get connected to the database. When i do the query using the file connection.cgi (with a select : select * from db) it returns me a lot of blank results, falling in a infinite loop of dead that kill my machine. I dunno what da hell im going bad, my table only have 2 records and perl see that 2 records and then populate my HTML-results page with
BreakLines.... Updated: i doesnt saw this perl script flush all the data of the query to ha hash, this make my computer hangs up, but if i print the output to the HTML, it prints me breaklines to the end of times. This its mi code

#!"C:\xampp\perl\bin\perl.exe"
use CGI qw(:all);
use DBI;
use CGI::Carp qw(fatalsToBrowser);
print "Content-type: text/html\n\n";

#Datos de la conexión
$db="db_jo151";
$host="localhost";
$port="3306";
$userid="db4_fabrik";
$passwd="secret!";


#Recibimos los datos del formulario
@campos = split(/\&/,$ENV{'QUERY_STRING'});
        foreach $valor (@campos) {
                ($nombre_variable, $dato) = split(/=/,$valor);
         print "$nombre_variable = $dato\n";
}

# Realizamos la conexión a la base de datos
$dbh = DBI->connect("DBI:mysql:database=$db;host=$host", $userid, $pas
+swd);


#Sentencia SQL
$query = qq(select * from joomladb_estados where est_matricula like '%
+$dato');
print "<h2>la query es -->$query</h2>";

# Primero hay que "preparar" el query
$sth = $dbh->prepare($query);

# Ejecutamos el query
$sth->execute or print "Ha fallado la consulta query";

#indicamos que vamos a imprimir HTML
print "<html><head><title>Consulta de Estado</title></head>\n";
print "<body>\n";
print "Variables -> $ENV{'QUERY_STRING'}\n";


# Recorremos los resultados obtenidos de la base de datos
%almacen;
while (($id, $fecha, $matricula) = $sth->fetchrow_array) {

    $almacen{$matricula}{'fecha_entrada'}=$fecha;
    $almacen{$matricula}{'estado'}=$estado;
}


print"</body></html>";

# Indicamos que hemos acabado y desconectamos
$sth->finish();
$dbh->disconnect;
exit 0;
[download]

Comment on Problem with mysql query using DBI::Mysql Download Code

Replies are listed 'Best First'.
Re: Problem with mysql query using DBI::Mysql by arc_of_descent (Hermit) on May 25, 2009 at 10:48 UTC
My suggestion would be to first NOT use MySQL at all, and check if your code can properly get the form data and print it out. This way you know what data the user is entering without the need to debug any mysql related problems. I also suggest you use the CGI methods to get the form data. You should be selective about what form data you need from the user. For example, to get the the value of a text input field named first_name, in your CGI script, you should do this: `my $first_name = param('first_name');` [download]	[reply] [d/l]
Re: Problem with mysql query using DBI::Mysql by FloydATC (Deacon) on May 25, 2009 at 10:00 UTC
Have you tried peeking at $DBI::errstr to see if the execute() works as expected? Also, I tend to use fetchrow_hashref() rather than fetchrow_array() because this lets me alter the table structure without fear of breaking anything.	[reply]
Re: Problem with mysql query using DBI::Mysql by CountZero (Bishop) on May 25, 2009 at 22:31 UTC
To start with, `use strict;` and Perl will tell you that `$estado` is never initialized. Next, your `while`-loop does not print anything. Finally, use placeholders in your SQL. That will protect you against a lot of evil. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l] [select]
Re: Problem with mysql query using DBI::Mysql by graff (Chancellor) on May 25, 2009 at 23:41 UTC
When writing a CGI script, it's very important to be careful about the use of print statements. When the script is invoked by a request from a browser, everything that is output via `print` statements in the script will be sent back to the browser. So, you want to make sure that you start with a valid HTTP header, then print a valid and complete html sequence (starting with `<HTML>` and ending with `</HTML>`). The CGI module has functions for making this easier and more manageable -- and as mentioned in an earlier reply, there are also functions for creating input methods on the web page, and for handling the query parameters that come back in the request from the form. Please look at the example code provided near the beginning of the CGI manual for guidance. Then look at how `print` is being (mis)used in the code you posted: your code never prints the http header (the CGI "header" function), and is printing some html content (and some non-html content) before doing `print "<html>..."` -- that won't work. As suggested above, get the http/html stuff working on its own first, then add the mysql stuff.	[reply] [d/l] [select]