variables inside SQL statements

[Anonymous Monk]

I have some SQL that I want to run within/outside (don't care which one) a Perl script. I can connect to svrmgrl and log in fine, but I need to use Perl variables (for paths) within the SQL statements.

[indigo]

Note - Interpolating variables into SQL is bad. Instead, use 'bind' variables, like so:

my $sql = 'INSERT INTO table ( path ) VALUES (:1)';

# Compile sql

my $sth = $dbh->prepare($sql) or die $dbh->errstr;

# Bind path to :1

$sth->execute('/path/to/this') or die $dbh->errstr;

[merlyn]

And if you use ? instead of :1, it'll work with any DBD, not just MySQL.

Rule number 3: be portable!

-- Randal L. Schwartz, Perl hacker

[indigo]

You mean there are databases other than mySQL and Oracle? I don't understand. :)

[dragonchild]

Go to the DBI docs and look up placeholders.

[Anonymous Monk]

nothing wrong with the previous answers for your precise need. If it weren't paths, perhaps a string like "Where's the beef O'Reilly?" you can also use:

$dbh->quote( "Where's the beef O'Reilly?" );

Originally posted as a Categorized Answer.

[AgentM]

huh? you can probably connect from within your perl script to the SQL database using <a href="http://www.unixodbc.com>unixODBC or the more popular albeit older alternative DBI. DBI provides question mark semantics that provide exactly the solution you are looking for...

Originally posted as a Categorized Answer.