Using HTML Form Values in MYSQL Statement

Trying to get form values and pass them to a query

[BDubs]

I am having trouble escaping text to use form values from my hash in order to build an insert query. This is fairly new to me so please point me to previous posts or any thoughts you may have. Thank you in advance, Brent.

My query string:

$query = 'INSERT INTO mm_contacts (name, email, phone, comments) VALUES (" \"$FORM{name}\", \"$FORM{email}\", \"$FORM{phone}\", \"$FORM{comments}\" " )';

Is coming out as follows:

INSERT INTO mm_contacts (name, email, phone, comments) VALUES (" \"$FORM{name}\", \"$FORM{email}\", \"$FORM{phone}\", \"$FORM{comments}\" " )

It needs to resolve to something like this, I believe:

$query = 'INSERT INTO mm_contacts (name, email, phone, comments) VALUES ("Testy Test", "[e-mail]", 216-875-8758, "This is a test")';

[perl_diver]

If you want to use variables in a string construct and have them expanded into their interpolated values you use a double-quoted string or a double-quoting string operator (like qq):

$query = qq{INSERT INTO mm_contacts (name, email, phone, comments) VALUES (" "$FORM{name}", "$FORM{email}", "$FORM{phone}", "$FORM{comments}" " )};

Not sure if you need all those double-quotes in the VALUES
But you want to look into using placeholders instead of plugging your perl variables directly into your INSERT statements.

[phranque]

welcome to WebmasterWorld [webmasterworld.com], BDubs!

variable interpolation does not occur within single quoted strings.
you could try using the string concatenation operator (.) for perl:
$query = 'INSERT INTO mm_contacts (name, email, phone, comments) VALUES ("' . $FORM{name} . '", "' . $FORM{email} . '", "' . $FORM{phone} . '", "' . $FORM{comments} . '" )';

[rocknbil]

Just to add to the above, you will also encounter problems depending on what quotes you use in your mySQL statements.

$FORM{'comments'} = 'This is a "cool site", thank you';

$query = qq{INSERT INTO mm_contacts (name, email, phone, comments) VALUES ("$FORM{name}", "$FORM{email}", "$FORM{phone}", "$FORM{comments}");};

When it goes to insert the comments value, mySQL will "think" the value ends at the first quote [This is a "] and won't know what to do with the second part of the value [cool site", thank you] and will error.

The two approaches to this are to escape the quote delimiter,

$FORM{'comments'} =~ s/"+/\\"/g;

Or to double the delimiter.

$FORM{'name'} =~ s/"+/""/g;
$FORM{'phone'} =~ s/"+/""/g;
$FORM{'email'} =~ s/"+/""/g;
$FORM{'comments'} =~ s/"+/""/g;

Of course, a loop or sub for this purpose would be more efficient)

The double delimiter allows the value to insert normally.

Two other bits of advice:

You may want to use single quotes in your select value delimiters; on the average, there are likely to be more double quoted values being stored, especially if html is any part of your input:

$query = qq{INSERT INTO mm_contacts (name, email, phone, comments) VALUES ('$FORM{name}', '$FORM{email}', '$FORM{phone}', '$FORM{comments}');};

... and treat it the same for the O'Malley's out there:

$FORM{'name'} =~ s/'+/''/g;
$FORM{'phone'} =~ s/'+/''/g;
$FORM{'email'} =~ s/'+/''/g;
$FORM{'comments'} =~ s/'+/''/g;

The second is, you should never ever allow form data to be directly used for database inserts or other program functions. It needs to be cleansed before doing an insert. The "basic rule" is if input data is stored anywhere (in a database) or is directly echoed back to the page, you open yourself to mySQL injection, Cross Site Scripting, or worse. Exchange "example.com" for your site, example.cgi for your script, any field for your input form:

http://www.example.com/cgi-bin/example.cgi?name=%22%3E%3Cscript%3Ealert%28123%29%3C%2Fscript%3E%22

The above is harmless, but if you run it and get a Javascript alert "123" your data is not being adequately cleansed and you have a potential injection problem.

[perl_diver]

Using placeholders should take of any problems and using concatenation is the wrong way to go. Its the slowest of perls string constructing functions.

[phranque]

- it depends on your application.
- sometimes brevity and simplicity trump a few milliseconds.
- as any perl aficionado knows, TIMTOWTDI!

[BDubs]

Thank you all very much for your help. I very much appreciate all the responses and the level of detail. As I learn more I will try to contribute.

Cheers, B