Help my syntax

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

Update: Thanks guys !!!! I really apreciate all the tips and helping me with my problem THANKS!!!!! ____________________________________________________________ Hello all monks
I am writing an web app to search a table in mysql using SELECT the search param are 2 fields, but I think I got something wrong in the line of the select this is the line :

  my $sql = "SELECT * FROM final WHERE pat = \"" . $PatenteToQuery . "
+\"" and WHERE ped = \"" . $PedimentoToQuery ."\";
[download]

And this is the whole file :

#!c:/perl/bin/perl.exe

use strict;

use DBI;
use CGI;



my $DB_Host = 'localhost';
my $DB_Name = 'proyecto';
my $DB_User = 'root';
my $DB_Pass = 'xyz123';



{  # begin main



  #--
  # Create a new CGI object to process the HTML form.

  my $cgi = new CGI;



  #--
  # Get the value of the "Patente" field from our HTML input form.

  my $PatenteToQuery = $cgi->param('Patente');
  my $PedimentoToQuery = $cgi->param('Pedimento');


  #--
  # Get a connection to the database server, aborting on failure.

  my $dbh = openDBConnection($DB_Host, $DB_Name, $DB_User, $DB_Pass);

  if ( ! defined $dbh ) {
    printErrorPage("Could not open a connection to the database!");
    exit;
  }



  #--
  # Define the query, prepare it, execute it then get the results as a
+n array of hash references.

  my $sql = "SELECT * FROM final WHERE pat = \"" . $PatenteToQuery . "
+\"" and WHERE ped = \"" . $PedimentoToQuery ."\";

  my $sth = $dbh->prepare($sql);

  $sth->execute();

  my $queryResults = $sth->fetchall_arrayref({});



  #--
  # Print the results as an HTML table.
  #
  # Note: the {'Last'} and {'First'} found below refer to column names
+ in the database table.

  print STDOUT "Content-Type: text/html\n";
  print STDOUT "\n";

  print STDOUT "<TABLE BORDER=\"1\">" . "\n";

  print STDOUT "<TR><TH>Patente</TH><TH>IMP/EXP</TH><TH>PEDIMENTO</TH>
+<TH>PED. ORIGINAL</TH><TH>CLAVE PEDIMENTO</TH><TH>FECHA</TH><TH>RFCAA
+A</TH><TH>CURPAA</TH><TH>PESOB</TH><TH>SUMA</TH><TH>BANCO</TH><TH>ARC
+HIVO</TH></TR>" . "\n";

  foreach my $i (0 .. $#$queryResults) {
    print STDOUT "<TR><TD>" . $queryResults->[$i]->{'pat'} . "</TD><TD
+>" . $queryResults->[$i]->{'impexp'} . "</TD></TD>" .   "</TD><TD>" .
+ $queryResults->[$i]->{'ped'} . "</TD><TD>" . $queryResults->[$i]->{'
+pedorig'} . "</TD><TD>" . $queryResults->[$i]->{'cveped'} . "</TD><TD
+>" . $queryResults->[$i]->{'fecha'} . "</TD><TD>" . $queryResults->[$
+i]->{'rfcaaa'} . "</TD><TD>" . $queryResults->[$i]->{'curpaa'} . "</T
+D><TD>" . $queryResults->[$i]->{'pesob'} . "</TD><TD>" . $queryResult
+s->[$i]->{'suma'} . "</TD><TD>" . $queryResults->[$i]->{'nombrebanco'
+} . "</TD><TD>" . $queryResults->[$i]->{'archivo'} ."\n";
  }

  print STDOUT "</TABLE>" . "\n";
  


  #--
  # Close our database connection.

  $dbh->disconnect();



}  # end main



# ====================================================================
+=====
#              F U N C T I O N    D E F I N I T I O N S
# ====================================================================
+=====



# --------------------------------------------------------------------
+-----
# |
# | Function  : printErrorPage
# |
# | Parameter : $errorMessage = the error message to print
# |
# | Return    : none
# |
# | Comments  : Prints an HTML error message.  This prints the "Conten
+t-Type"
# |             HTTP header.
# |
# --------------------------------------------------------------------
+-----

sub printErrorPage
{
  my $errorMessage = shift;

  print STDOUT "Content-Type: text/html\n";
  print STDOUT "\n";

  print STDOUT "<H1>" . $errorMessage. "</H1>\n";

}



# --------------------------------------------------------------------
+-----
# |
# | Function  : openDBConnection
# |
# | Parameter : $host     = the name or ip of the database server
# | Parameter : $db       = the name of the database to connect to
# | Parameter : $username = the username to use when connecting to the
+ MySQL server
# | Parameter : $password = the password to use when connecting to the
+ MySQL server
# |
# | Return    : A handle to the database connection.
# |
# | Comments  :
# |
# --------------------------------------------------------------------
+-----

sub openDBConnection
{
  my $host     = shift;
  my $db       = shift;
  my $username = shift;
  my $password = shift;

  my $dbh;  # Handle to a database connections.

  $dbh = DBI->connect("DBI:mysql:$db:$host",
                      $username, $password,
                      { RaiseError => 0, AutoCommit => 1 }
                     );

  if ( ! defined ($dbh) ) {
    return undef;
  }
  else {
    return $dbh;
  }

}
[download]

Can some one help me point the error that I have please Thank you

Comment on Help my syntax Select or Download Code

Replies are listed 'Best First'.
Re: Help my syntax by jettero (Monsignor) on Dec 21, 2007 at 19:32 UTC
Don't do things like `pat = \"" . $PatenteToQuery . "\"` or strangers will ruin your databases for fun. Use the bind variables instead. It will save you trouble in terms of security and in terms of hunting down this bug. `my $sql = "SELECT * FROM final WHERE pat = ? and WHERE ped = ?"; my $sth = $dbh->prepare($sql); $sth->execute( $PatenteToQuery, $PedimentoToQuery );` [download] In this case, your error was a missing end quote, which I found while installing the bind vars. UPDATE: Yes, my mistake, I reproduced the bad SQL syntax while fixing the bad perl syntax. Honestly, I didn't even read the SQL. Sorry about that. -Paul	[reply] [d/l] [select]
Re^2: Help my syntax by ArmandoG (Sexton) on Dec 21, 2007 at 20:14 UTC
Hi thanks for writing! I try it like you told me but is there something more I need to do to make it work because it just shows me the result page but without any data, and where I go to check the error log, this is what It said: [Fri Dec 21 14:10:22 2007] [error] [client 127.0.0.1] DBD::mysql::st e +xecute failed: You have an error in your SQL syntax; check the manual + that corresponds to your MySQL server version for the right syntax t +o use near 'WHERE ped = '7010700'' at line 1 at c:\oracle\ora92\apach +e\apache\cgi-bin\patyped.pl line 55. [Fri Dec 21 14:10:22 2007] [error] [client 127.0.0.1] DBD::mysql::st f +etchall_arrayref failed: fetch() without execute() at c:\oracle\ora92 +\apache\apache\cgi-bin\patyped.pl line 60. [download] What is missing in there? Thanks :o)	[reply] [d/l]
Re^3: Help my syntax by chromatic (Archbishop) on Dec 21, 2007 at 20:33 UTC
That line should be: `my $sql = "SELECT * FROM final WHERE pat = ? and ped = ?";` The second `WHERE` is an error, per my reading of the MySQL documentation.	[reply] [d/l] [select]
Re: Help my syntax by ww (Archbishop) on Dec 21, 2007 at 19:40 UTC
The errors as reprted by `perl -c syntax.pl`: Bareword found where operator expected at 17.pl line 65, near "print S +TDOUT "Content" (Might be a runaway multi-line "" string starting on line 50) (Do you need to predeclare print?) Backslash found where operator expected at 17.pl line 65, near "html\" String found where operator expected at 17.pl line 66, near "print STD +OUT "" (Might be a runaway multi-line "" string starting on line 65) (Missing semicolon on previous line?) Backslash found where operator expected at 17.pl line 66, near "print +STDOUT "\" (Do you need to predeclare print?) String found where operator expected at 17.pl line 68, near "print STD +OUT "" (Might be a runaway multi-line "" string starting on line 66) (Missing semicolon on previous line?) Global symbol "$sth" requires explicit package name at 17.pl line 50. Global symbol "$sql" requires explicit package name at 17.pl line 50. Global symbol "$sth" requires explicit package name at 17.pl line 50. Global symbol "$queryResults" requires explicit package name at 17.pl +line 50. Global symbol "$sth" requires explicit package name at 17.pl line 50. syntax error at 17.pl line 65, near "print STDOUT "Content" Global symbol "$queryResults" requires explicit package name at 17.pl +line 72. Global symbol "$queryResults" requires explicit package name at 17.pl +line 73. Global symbol "$queryResults" requires explicit package name at 17.pl +line 73. Global symbol "$queryResults" requires explicit package name at 17.pl +line 73. Global symbol "$queryResults" requires explicit package name at 17.pl +line 73. Global symbol "$queryResults" requires explicit package name at 17.pl +line 73. Global symbol "$queryResults" requires explicit package name at 17.pl +line 73. Global symbol "$queryResults" requires explicit package name at 17.pl +line 73. Global symbol "$queryResults" requires explicit package name at 17.pl +line 73. Global symbol "$queryResults" requires explicit package name at 17.pl +line 73. Global symbol "$queryResults" requires explicit package name at 17.pl +line 73. Global symbol "$queryResults" requires explicit package name at 17.pl +line 73. Global symbol "$queryResults" requires explicit package name at 17.pl +line 73. [download] Hint: `"\""`	[reply] [d/l] [select]
Re: Help my syntax by Anonymous Monk on Dec 21, 2007 at 19:37 UTC
As a stylistic issue, in SQL it's probably best to specify the columns you want returned (`SELECT THIS, THAT, THE_OTHER`...) vs using `SELECT *`; the table you're reading may have columns added or rearranged.	[reply]
Re^2: Help my syntax by Cop (Initiate) on Dec 22, 2007 at 03:34 UTC
Bless you. How many times in the past I was worried about the select * problem. It introduces the potential of breaking receiving structures. Anyone answered the question without pointing out that is a nut.	[reply]
Re: Help my syntax by Cop (Initiate) on Dec 21, 2007 at 20:10 UTC
I don't know how many times we have to repeat this: placehold. Your problem is not about syntax but knowledge.	[reply]