Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

database selecting then validating

by BluGeni (Novice)
on May 24, 2012 at 18:45 UTC ( #972291=perlquestion: print w/ replies, xml ) Need Help??
BluGeni has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to validate with the database that the employee number entered matches with what the data base has stored and if it does run my code if not stop it. what i have--
$statement = "select * from users where empid = ".$empid." and dept = +'".$dept."'";
basically i want it to run if empid and dept are matched in the row that is stored in the database table thank you for any help you can offer

Comment on database selecting then validating
Download Code
Re: database selecting then validating
by choroba (Abbot) on May 24, 2012 at 19:09 UTC
    And the question is ...?

    Also, see Bobby Tables and imagine someone enters

    0 ; drop table users ; --
    as his/her empid.
      how do i implement an if statement to run my code if this checks out?
Re: database selecting then validating
by poj (Priest) on May 24, 2012 at 19:39 UTC
    my $sql = 'select count(*) from users where empid=? and dept=?'; my ($count) = $dbh->selectrow_array($sql,undef,$empid,$dept); if ($count == 1){ # valid } else { # not valid }

      You could simplify that into (assuming a unique constraint on (empid, dept)):

      my $sql = 'select 1 from users where empid=? and dept=?'; if ($dbh->selectrow_array($sql, undef, $empid, $dept)) { # valid } else { # not valid }

      ...I'm always a bit wary of using Count(*) for exists-or-not-exists queries

Re: database selecting then validating
by sundialsvc4 (Monsignor) on May 24, 2012 at 20:26 UTC

    poj’s response incorporates the basic ideas here, whether or not this is exactly the strategy you want to use in your particular case.   There are, natcherly, several ways to do it.   But the most important feature of all of them is that all of the user-provided values are presented to the SQL query by means of placeholders, which are the (notice, not-quoted) question marks.

    Incidentally, if you do this a lot (say, tens of thousands of times...) you can save a perhaps-significant amount of time by “preparing” the query once, obtaining a “statement handle,” which you then use to execute the same query (substituting different input-values for the placeholders each time).   You will never, ever have a “Bobby Tables problem” because the user-provided input is never regarded as “part of” the SQL that is to be executed:   it is, as it were, an input-variable.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://972291]
Approved by herveus
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (7)
As of 2014-08-20 11:47 GMT
Find Nodes?
    Voting Booth?

    The best computer themed movie is:

    Results (111 votes), past polls