Perl DBI issue with apostrophes 1

[CherylD]

I need help with an insert statement. Here's the situation:
- cgi script uses perl to load up data from a mysql database onto html page
- that data can then be altered and inserted back into the database

This all works, only if there are no apostrophes in the data (each field is a variable). How do I avoid the apostrophes?

 

[sophisticate]

Before creating the insert statement, check all the variables you're getting from the form. Escape out (place a \ before) any characters that would result in a SQL error (namely " and '). You can do this with a simple s/// statement:

$var =~ s/([\"\'])/\\$1/g;

Put other to-be-excluded items in between the square brackets.

Hope this helps,

brendanc@icehouse.net

 

[CherylD]

Thank you so much....I almost had that working, but didn't have the syntax quite right.
A nice and quick respone too

 

[tsdragon]

If you put "placeholders" (?) in your sql statement and use the execute(value-list) form you don't have to worry about special characters in your values.
Tracy Dryden
tracy@bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[bitwise]

Heads up to people out there who haven't noticed the problem CherylD did. You should always do this to all SQL statements that are modified by the user, namely backslash out ' and any other special characters. If you didn't - a hacky user could close off an SQL statement then run another one.

For Example if your typical statement was formed like this:
$query = "UPDATE tbl_User SET Name='$var' WHERE ID=1";

and $var is pulled from a edit box control on a form then something like this could be typed in the edit box:

MyName', AnotherField='HaHa

Now your SQL Statement looks like this:

$query = "UPDATE tbl_User SET Name='MyName', AnotherField='HaHa' WHERE ID=1"

Perfectly fine! You could also close off the statement with a '; and then run a DELETE command - opps!

Just a warning,
bitwise