homepage Welcome to WebmasterWorld Guest from 54.198.42.105
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

    
Using HTML Form Values in MYSQL Statement
Trying to get form values and pass them to a query
BDubs




msg:3969520
 1:12 am on Aug 11, 2009 (gmt 0)

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", "test@test.com", 216-875-8758, "This is a test")';

 

perl_diver




msg:3969567
 4:05 am on Aug 11, 2009 (gmt 0)

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




msg:3969729
 12:56 pm on Aug 11, 2009 (gmt 0)

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




msg:3969865
 3:35 pm on Aug 11, 2009 (gmt 0)

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




msg:3969978
 5:58 pm on Aug 11, 2009 (gmt 0)

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




msg:3970008
 6:42 pm on Aug 11, 2009 (gmt 0)

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

BDubs




msg:3971920
 1:55 pm on Aug 14, 2009 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved