Try this

$ssn = q{%} . $ssn;

my $sql = <<"END"
SELECT 
  SSN, RTRIM(LAST_NAME,' '), 
  TO_CHAR(BIRTH_DATE,'YYYY'), 
  STORE
FROM 
  PYHADMIN.PY_EMP_SHADOW
where 
      TO_CHAR(BIRTH_DATE,'YYYY') like ?
  and SSN like ?
  and RTRIM(LAST_NAME,'  ') like upper(?)
  and rownum <= 1
END
;
my $yi = $dbh->prepare($sql);
$yi->execute($year, $ssn, $name );
[download]

Definitely always use placeholders; even more, as the OP mentions an ORA error - sign that Oracle is the underlying DB - I'd go with something like the following:


my ($db_ssn, $db_lastname, $db_birth_year, $db_store);

  my $sql = <<'END_SQL';
SELECT ssn, RTRIM(last_name,''),TO_CHAR(birth_date,'YYYY'),store
  FROM pyhadmin.py_emp_shadow
 WHERE TO_CHAR(birth_date,'YYYY') LIKE :birth_year
   AND ssn LIKE :ssn
   AND RTRIM(last_name,'  ') LIKE UPPER(:last_name)
   AND rownum <= 1
END_SQL

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

     $sth->bind_param( ':birth_year', $year,      ORA_NUMBER );
     $sth->bind_param( ':ssn',        $ssn,       ORA_NUMBER );
     $sth->bind_param( ':last_name',  $last_name, ORA_VARCHAR2);

     $sth->execute();

  ($db_ssn, $db_lastname, $db_birth_year, $db_store)
     = $sth->fetchrow_array;

     $sth->finish();
};
if ($@) {
   # there was an fatal error; die() if you want, 
   # but maybe you can do something to recover and go on ,
   # log something, etc.
}
[download]

Have a look on DBD::Oracle for this special form of placeholders.

These aren't portable, so avoid them if your code is going to run on other DBs, but as long as you're on Oracle, the code is much easier to read/maintain like this. Also, it is a lot harder to make (typical, sometimes hard to debug) errors like swapping values for the placeholders - think about more complex queries, where you would need to (re)use the same value in more then one place.

Just to be clear with your example, you are "quoting" SSN? So if I dont need to both SSN rather just quote the lastname, I just change the $SSN values near the top to $name?

All the variables in the execute() call are being 'quoted' as it were.

I had to add the '%' to the ssn in order for your wild card search to work.

There are several ways to handle this - placeholders, DBI's quote method or the q{} operator.

$name = $dbh->quote( $name );
[download]

derby: ++!

Update: Oops! Herkum beat me to the punch!/Update

Seconded! You should definitely check out the placeholder syntax, as it fixes these problems for you. Something like the following should work for you:

my $SQL = "SELECT col1, col2 FROM table WHERE lastname=?";
my $ST = $DB->prepare($SQL) or die;
$ST->execute($last_name) or die;
[download]

...roboticus

Consider what will happen to your query with $name = "; DELETE PY_EMP-SHADOW;" (← deliberately misspelled for your protection). The semicolons will end the original SELECT statement that you started and begin a new, statement that you did not intend. It will delete that important data you are keeping!! A malicious person could take other actions as well. This is called "SQL injection".

Constructing a SQL query using user-supplied strings is a very bad habit to get into for this reason. Using placeholders (the approach roboticus and others are recommending) is a much better approach. Use it whenever possible. Placeholders also take care of all those nasty problems with quotes and other special characters. If a user enters their name as "Jim; Delete ...", using placeholders, that is precisely what his name will be set to. No wierd side-effects.

Is the method Herkum is talking about using placeholders? I don't understand these placeholder I guess.

$name = q{%} . $name;

my $sql = <<"END"
SELECT
  SSN, RTRIM(LAST_NAME,' '),
  TO_CHAR(BIRTH_DATE,'YYYY'),
  STORE
FROM
  PYHADMIN.PY_EMP_SHADOW
where
      TO_CHAR(BIRTH_DATE,'YYYY') like '$year'
  and SSN like '%$ssn'
  and RTRIM(LAST_NAME,'  ') like upper('$name')
  and rownum <= 1
END
;
my $yi = $dbh->prepare($sql);
$yi->execute();
#--------Send the values from the query into variables
($SSNc, $LAST_NAMEc, $YEARc, $storenum) = $yi->fetchrow_array;
[download]

Please use Herkum's method - use placeholders.

As a short introduction, placeholders mean you put a question mark anywhere you'd put (quoted or unquoted) input when you prepare() your sql query, and then supply the values when you execute() the query:

my $sth = $dbh->prepare("SELECT something FROM something_else WHERE co
+l1 = ? OR col2 = ?");

$sth->execute($value1,$value2);

while (my ($result) = $sth->fetchrow) {
  # do something with $result
}
[download]

Using placeholder guarantees your values will always be "quoted" correctly, regardless of what's in them.
"What should it profit a man, if he should win a flame war, yet lose his cool?"

What do you mean by $results, where does this value come from? I guess I don't really understand when using placeholders, how do I do a $fetchrow on the values that have already ben executed?