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

Hello:

I'm building a program that holds data of court cases.

I'm making a search script for a user to search for a specific case.

Let's say Attorney Jim wants to search for a case that had a issue of "Allowance" and "input1" was who filed/inputted the report into MySQL database.

I've been working about 30 minutes now trying to figure out why I'm getting this error. Althought im not 100% sure that the error is coming from DBI syntax or MySQL syntax, I think its coming from MySQL Syntax. 

use DBI;

$dbh = DBI->connect('DBI:mysql:dataflex') or die "Couldn't connect to 
+database: " . DBI->errstr;

$sql = "SELECT * FROM dataflex where attorney = '$INPUT{'attorney'}' a
+nd where issue = '$INPUT{'issue'}'";
$sth = $dbh->prepare($sql) or print "preparing: ",$dbh->errstr;
$sth->execute or print "executing: ", $dbh->errstr;
}

  $dbh->disconnect;

[download]
Prints out:

executing: You have an error in your SQL syntax near 'where issue = 'Allowance'' at line 1

Anyone know why it's giving me a syntax error?

Thank you,
Anthony

Replies are listed 'Best First'.
Re: DBI and MySQL
by Kanji (Parson) on Jul 12, 2002 at 06:06 UTC

    As you suspect, it is your (My)SQL syntax and not Perl/DBI at fault here, and can be fixed by removing the second WHERE in your statement because (in simple queries, anyway) you're only allowed one WHERE clause.

    Something else you might want to do is investigate the use of placeholders (example below), which will automatically quote your variables so they don't break your SQL (ie, if you searched for O'Reilly, you'd have an extra apostophe in your statement!).

    $sql = "
  SELECT * 
  FROM   dataflex 
  WHERE  attorney = ?
    AND  issue    = ?
";

$sth = $dbh->prepare($sql) 
   or die "preparing: ", $dbh->errstr;

$sth->execute($INPUT{'attorney'},$INPUT{'issue'})
   or die "executing: ", $dbh->errstr;

    [download]

    Note also the use of die instead of print, so that the script finishes after the first error instead of generating an endless stream of them, potentially confusing things...

        --k.


[reply]
[d/l]
Re: DBI and MySQL
by Trimbach (Curate) on Jul 12, 2002 at 11:42 UTC
    It's also worth noting that placeholders also let you avoid the nastiness from having someone send your program f*cked input in order to get at data they're not supposed to. I'm guessing by the name of %INPUT that that hash is populated from data from the outside world, so, what if instead of $INPUT{'attorney'} = "Tom" (a legitimate value), some Evil person makes $INPUT{'attorney'} = "Tom' AND supersecret='yes'"

    Assuming you removed the extra WHERE, your SQL would evaluate to this in your original code: 

    SELECT * FROM dataflex where attorney = 'TOM' AND supersecret='yes' an
+d issue = 'some issue'

    [download]
    Not exactly what you expected, yes? Using placeholders, on the other hand, ensures that this doesn't happen. Here's what it would (mostly) look like (note the escaping of the single quotes): 
    <code>
SELECT * FROM dataflex where attorney = 'TOM\' AND supersecret=\'yes\'
+' AND supersecret='yes' and issue = 'some issue'

    [download]
    The single quotes around the malicious extra arguments are escaped, which means the WHOLE value (TOM' AND supersecret='yes') is checked for in the attorney column (which, dollars to donuts, won't match anywhere.) This prevents EvilDoers from getting at data they ain't supposed to get.

    Placeholders are a nearly unqualified good. Learn to love them.

    Gary Blackburn
    Trained Killer

[reply]
[d/l]
[select]
Re: DBI and MySQL
by George_Sherston (Vicar) on Jul 12, 2002 at 09:36 UTC
    I think you've got a "where" too many. But this is also a situation where placeholders could save you grief. Also, your laudable error-checking wd be even better with die instead of print. Try the following (obviously unchecked): 
    $dbh = DBI->connect('DBI:mysql:dataflex') or die "Couldn't connect to 
+database: " . DBI->errstr;

$sql = "SELECT * FROM dataflex where attorney = ? and issue = ?";
$sth = $dbh->prepare($sql) or die "preparing: ",$dbh->errstr;
$sth->execute($INPUT{'attorney'},$INPUT{'issue'}) or die "executing: "
+, $dbh->errstr;

    [download]


    § George Sherston
[reply]
[d/l]
[select]
Re: DBI and MySQL
by talexb (Chancellor) on Jul 12, 2002 at 13:44 UTC
    You've already got some good comments on how to approach your problem.

    Here's where you can really simplify your life: make sure the query does what you want from the SQL prompt. Once that's working, then you can use the query that produces the correct result in your Perl script. Don't try to solve two problems at once .. divide and conquer.

    --t. alex

    "Mud, mud, glorious mud. Nothing quite like it for cooling the blood!" --Michael Flanders and Donald Swann

[reply]
Re: DBI and MySQL
by dmmiller2k (Chaplain) on Jul 12, 2002 at 14:04 UTC

    Perhaps this isn't a problem, but it jumped out at me...

    No one else mentioned this, but it's possible your extra 'where' keyword is also masking a quoting issue. In the following line: 

    $sql = "SELECT * FROM dataflex where attorney = '$INPUT{'attorney'}' a
+nd where issue = '$INPUT{'issue'}'";

    [download]

    note that the single quotes surrounding $INPUT{'attorney'} and $INPUT{'issue'} conflict with those surrounding the hash keys, 'attorney' and 'issue'. Since hash keys may be bare words, you can circumvent this by removing the single quotes around both 'attorney' and 'issue' (of course getting rid of the extra 'where'): 

    $sql = "SELECT * FROM dataflex where attorney = '$INPUT{attorney}' and
+ issue = '$INPUT{issue}'";

    [download]

    dmm

    If you GIVE a man a fish you feed him for a day
    But,     TEACH him to fish and you feed him for a lifetime
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom