Passing a perl variable in SQL AND statement

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

Hi,

I am trying to execute a SQL query from my Perl script. The and statement relies on the variable that I have outside of SQL query.

It doesn't seem to work for some reason.

Here is a part of my code where I am trying to accomplish this:

my $dbh = DBI->connect( "dbi:Oracle:host=ortp09-scan;port=1521;service
+_name=service1", "uname", "pass" , {
    RaiseError => 1
} );

# $day variable could change..hardcoded for now
$day = "Sunday";

my $sth = $dbh->prepare(<<SQL);
Select *
From SMSVA_SUPR.schedule_table
where process_name = 'Seed'
and '$day' = 'Y';
SQL

$sth->execute();
[download]

Any help would be appreciated. Thanks.

Comment on Passing a perl variable in SQL AND statement Download Code

Replies are listed 'Best First'.
Re: Passing a perl variable in SQL AND statement by clueless newbie (Curate) on Nov 20, 2013 at 20:45 UTC
This `and '$day' = 'Y';` should probably read `and $day = 'Y';`	[reply] [d/l] [select]
Re^2: Passing a perl variable in SQL AND statement by vihar (Acolyte) on Nov 20, 2013 at 20:58 UTC
Thank you for your reply. I tried that. I still get SQL error. DBD::Oracle::db prepare failed: ORA-00911: invalid character (DBD ERRO +R: error possibly near <> indicator at char 84 in 'Select From SMSVA_SUPR.schedule_table where process_name = 'Seed' and Sunday = 'Y'<>; ') [for Statement "Select From SMSVA_SUPR.schedule_table where process_name = 'Seed' and Sunday = 'Y'; "] at seed_driver.pl line 93. DBD::Oracle::db prepare failed: ORA-00911: invalid character (DBD ERRO +R: error possibly near <> indicator at char 84 in 'Select From SMSVA_SUPR.schedule_table where process_name = 'Seed' and Sunday = 'Y'<>; ') [for Statement "Select From SMSVA_SUPR.schedule_table where process_name = 'Seed' and Sunday = 'Y'; "] at seed_driver.pl line 93. [download]	[reply] [d/l]
Re^3: Passing a perl variable in SQL AND statement by dasgar (Priest) on Nov 20, 2013 at 21:06 UTC
Based on what the error message is complaining about, I'd recommend that you try to remove the semi-colon at the end of the SQL statement. Also, I don't know where your variable will be getting it's values from, but you might want to check out Little Bobby Tables for info about preventing SQL injection issues.	[reply]
Re^4: Passing a perl variable in SQL AND statement by vihar (Acolyte) on Nov 20, 2013 at 21:18 UTC
Re^2: Passing a perl variable in SQL AND statement by vihar (Acolyte) on Nov 20, 2013 at 20:59 UTC
I pretty much tried all combinations of using it without quotation marks, with ' ' and with " " and I keep getting those SQL errors.	[reply]
Re^3: Passing a perl variable in SQL AND statement by vihar (Acolyte) on Nov 20, 2013 at 21:19 UTC
That was a silly mistake on my end. I took out ; and it worked. Thanks for your help!	[reply]