mysql and perl - adding variable into query

Question

Hi all you mysql and perl gurus!

I have code that looks like $store = qq(INSERT INTO main (release_date) VALUES(DATE_ADD(NOW(), INTERVAL 1 DAY))

It works perfectly. The problem is I need to find a way to use a variable instead of "INTERVAL 1 DAY". This value is coming from a form and has about 12 options (ie: 1 Day, 2 Day, 1 Week, 2 Week).

I'm currently using 12 different if/elsif conditions based on the form variables so the code right now is really bulky.

I'm trying to get something like this to work var timeframe = ""; if ($data{date_field} == "1 Week") { $timeframe = "1 WEEK"; } . . $store = qq(INSERT INTO main (release_date) VALUES(DATE_ADD(NOW(), INTERVAL "$timeframe"))

But it errors out saying I have a MySQL syntax problem.

Anyone know what I have to do to get this to work so I can shorten the code a bit?

perhaps the problem is the quotes. I would try removing the quotes from around $timeframe. Can you output the value of store before running it? lets see what it is. — user2932397
– user2932397, Commented Nov 22, 2013 at 0:37
I strongly encourage you to heed the advice in your previous question to use placeholders in your SQL statement, and do the prepare only once. — Borodin
– Borodin, Commented Nov 22, 2013 at 2:42

Borodin · Accepted Answer · 2013-11-22 02:44:01Z

3

Lose the quotes; $bar = "baz"; qq(foo $bar) will be foo baz, while qq(foo "$bar") gives you foo "baz", which you don't want in this case.

However, specifically for SQL, this approach is somewhat dangerous. In your case it is fine, since you're setting the value of $timeframe yourself; but if there is a faintest possibility of user input getting in there, you can have a Bobby Tables incident. Specifically, study the Perl page there.

edited Nov 22, 2013 at 2:44

Borodin

127k9 gold badges72 silver badges146 bronze badges

answered Nov 22, 2013 at 0:35

Amadan

200k23 gold badges252 silver badges321 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Anderson Over a year ago

Thank you! That was absolutely what I needed! It works great!

Collectives™ on Stack Overflow

mysql and perl - adding variable into query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related