Inserting a string in MYSQL through Perl

Question

The problem I am having is that I have a user input a string... Could be anything less than 156 characters, and the DBI statement inserts the string perfectly if there are no apostrophes or quotation marks in the string.

This works fine-> I am working! This doesn't work-> I'm not working!

To insert I use the following code:

    $sth = $dbh->prepare("INSERT INTO table VALUES('$var1','$var2','$var3')");
    $sth->execute();

I know that Perl interpolates the strings different with ' ' and " " but my program throws a fit when I try that. I've also tried using a join to join that prepare statement together using " " marks. The variable that stores the 155 characters is $var3 if that matters, and all 3 are VARCHAR attributes. Any suggestions?

score 6 · Accepted Answer · edited May 23 '17 at 12:27

6

You need to escape your variables.

It is exactly this oversight that hackers exploit to perform SQL injection attacks: read the story of poor little Bobby Tables. Indeed, you really should avoid ever evaluating your variables for SQL, by parameterising your prepared statement:

$sth = $dbh->prepare('INSERT INTO table VALUES(?, ?, ?)');
$sth->execute($var1, $var2, $var3);

edited May 23 '17 at 12:27

Community

1
1

answered Oct 14 '12 at 23:01

eggyal

113,121
18
188
221

so will the $var1 replace the ? mark when I run those 2 lines of code? – James Brown Oct 14 '12 at 23:08
@JamesBrown: Yes, the arguments to `execute()` will replace each `?` placeholder in turn. – eggyal Oct 14 '12 at 23:09
ah, you are my hero! Thanks for the help and tips! – James Brown Oct 14 '12 at 23:11

Inserting a string in MYSQL through Perl

1 Answers1