Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

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

Replies are listed 'Best First'.
Re: database selecting then validating
by choroba (Bishop) 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 (Prior) 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 (Abbot) 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
[ambrus]: 1nickt: the tea didn't have sugar or honey or lemon or milk. or even caffeine in a significant quantity. it's really "colored water", warm water with some fruit smell to make it more pleasant than ordinary warm water.
[ambrus]: 1nickt: in theory, it shouldn't hurt the electronics, but also could temporarily cause problems until it dries, and the inside of the keyboard doesn't dry quickly, because it's closed.
[ambrus]: 1nickt: that's the theory. the theory is also that it should have dried by Sunday morning, but it didn't. so I dunno.
[ambrus]: I'll try today evening if it works now.

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (11)
As of 2017-11-21 14:39 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (302 votes). Check out past polls.