Page 1 of 1
Not escaping properly
Posted: Wed Apr 15, 2009 11:35 pm
by drschwartz
I'm trying to store some form-based data into a MySQL table. In the php, I run the input field data in $POST through the following function:
function mysqlclean($array, $index, $maxlength, $connection)
{
if (isset($array["{$index}"]))
{
$input = substr($array["{$index}"], 0, $maxlength);
$input = mysql_real_escape_string($input, $connection);
return ($input);
}
return NULL;
}
Here's how I call the function:
$jewish_org = mysqlclean($_POST, "jewish-org", 75, $con);
$howd_hear = mysqlclean($_POST, "howd-hear", 75, $con);
And this is the error I get when I attempt to execute the query (the 'N's are from some checkboxes in the form; is it odd that I don't see any of the preceding fields in the query?):
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\"Don\'t mess with me\", \"I didn\'t mess with you\", 'N', 'N', 'N', 'N', 'N', '' at line 2
The values of the two entry fields were:
"Don't mess with me"
"I didn't mess with you"
There doesn't appear to be an ending single quote on the escaped strings.
Any help would be appreciated!
TIA,
David
Re: Not escaping properly
Posted: Thu Apr 16, 2009 12:03 am
by McInfo
When you post code, please use the
tags (where php is php, text, or html). There is a "Code" button above the textarea where you edit your post.
Error messages show a limited number of characters around where the error occurred, so no, it's not odd that you don't see part of your query.
It would be helpful if I could see what the entire query string looks like. Use the following code to display the query. $query is the variable that holds the string that you are about to send to mysql_query().
Something slightly unrelated:
In your mysqlclean() function definition, you can use
Code: Select all
$array[$index]
// instead of
$array["{$index}"]
Edit: This post was recovered from search engine cache.
Re: Not escaping properly
Posted: Thu Apr 16, 2009 8:58 am
by drschwartz
The entire query is as follows:
$sql="INSERT INTO jcc_participant (part_id, household_rep, street_address, city, state, zipcode, phone, email, need_mentor, jewish_org, howd_hear, others, present, marketing, website, contribute, mentor)
VALUES (NULL, $household_rep, $street_address, $city, $state, $zipcode, $phone, $email, $need_mentor, $jewish_org, $howd_hear, $others, $present, $marketing, $website, $contribute, $mentor )";
Thanks for your help and I'll use those tags in the future (sorry about that).
David
Re: Not escaping properly
Posted: Thu Apr 16, 2009 12:03 pm
by McInfo
drschwartz wrote:Thanks for your help and I'll use those tags in the future (sorry about that).
Here is a good place to use
tags:
Code: Select all
$sql="INSERT INTO jcc_participant (part_id, household_rep, street_address, city, state, zipcode, phone, email, need_mentor, jewish_org, howd_hear, others, present, marketing, website, contribute, mentor)
VALUES (NULL, $household_rep, $street_address, $city, $state, $zipcode, $phone, $email, $need_mentor, $jewish_org, $howd_hear, $others, $present, $marketing, $website, $contribute, $mentor )";
I need that code, but I also need the computed value of $sql. You can get that with
In your browser's HTML source code, select everything from { to } and post it here (inside
tags, please).
I suspect that you are missing some single-quotes around the values in your query. If you want to be able to insert NULLs into your database table, you can change your function to
Code: Select all
function mysqlclean ($array, $index, $maxlength, $connection)
{
if (isset($array[$index]))
{
$input = substr($array[$index], 0, $maxlength);
$input = mysql_real_escape_string($input, $connection);
return "'$input'";
}
return 'NULL';
}
If you change your function to this, do not add single-quotes to your query. The function does it for you. When the function returns a NULL, it is returned as a string. The quotes in the return 'NULL' statement will not appear in your query. This is by design.
Edit: This post was recovered from search engine cache.
Re: Not escaping properly
Posted: Thu Apr 16, 2009 12:21 pm
by drschwartz
Will do. However, I'm not sure what you mean by:
"In your browser's HTML source code, select everything from { to } ..."
My HTML markup doesn't include braces, right? I must be missing something.
This is volunteer PHP work I'm doing so I'll get to this tonight after I earn some money!!
Thanks again for your help,
David
Re: Not escaping properly
Posted: Thu Apr 16, 2009 1:30 pm
by McInfo
If you use
the query will be bounded by (begin and end with) braces. The braces are there to more easily identify where the query begins and ends.
When you view your page in your Web browser, use the "view source" command to see the HTML source code.
- In Firefox, use the menu: View > Page Source (CTRL+U).
- In Internet Explorer, use the menu: View > Source.
- In a different browser, it is something similar.
In the HTML source code, find the string that begins with { and ends with }, then copy-and-paste that into a forum post. I need the string from the HTML source and not the Web page because your browser does a thing called entity decoding; so the string on the page and the string in the source could be different.
Edit: This post was recovered from search engine cache.
Re: Not escaping properly
Posted: Thu Apr 16, 2009 5:52 pm
by drschwartz
Here you go:
Code: Select all
{INSERT INTO jcc_participant (part_id, household_rep, street_address, city, state, zipcode, phone, email, need_mentor, jewish_org, howd_hear, others, present, marketing, website, contribute, mentor) VALUES (NULL, "aas", "", "", "", "98006", "", "aas@aadfa.com", 'N', \"Don\'t mess with me\", \"I didn\'t mess with you\", 'N', 'N', 'N', 'N', 'N', 'N' )}
and, for good measure, here's the code that sets those variables:
Code: Select all
// clean up the values
$household_rep = shellclean($_POST, "household_rep", 60);
$street_address = shellclean($_POST, "street_address", 50);
$city = shellclean($_POST, "city", 50);
$state = shellclean($_POST, "state", 2);
$zipcode = shellclean($_POST, "zipcode", 10);
$phone = shellclean($_POST, "phone", 15);
$email = shellclean($_POST, "email", 50);
$jewish_org = mysqlclean($_POST, "jewish-org", 75, $con);
$howd_hear = mysqlclean($_POST, "howd-hear", 75, $con);
// set the checkbox values
if(isset($_POST['mentor'])) $mentor="'Y'";
else $mentor="'N'";
if(isset($_POST['others'])) $others="'Y'";
else $others="'N'";
if(isset($_POST['present'])) $present="'Y'";
else $present="'N'";
if(isset($_POST['marketing'])) $marketing="'Y'";
else $marketing="'N'";
if(isset($_POST['website'])) $website="'Y'";
else $website="'N'";
if(isset($_POST['contribute'])) $contribute="'Y'";
else $contribute="'N'";
if(isset($_POST['need_mentor'])) $need_mentor="'Y'";
else $need_mentor="'N'";
FYI, once this issue is resolved, I was planning to use #mysqlclean for all the entry fields.
David
Re: Not escaping properly
Posted: Thu Apr 16, 2009 7:43 pm
by McInfo
The problem is that the second through the eighth values in your query have unescaped double-quotes. I'm glad you posted the additional code because now I can tell you that the problem is in your shellclean() function. It inserts quotes around the values that are in conflict with the quotes around your query.
Is the shellclean() function an old version of the mysqlclean() function? Maybe you need to just use mysqlclean() instead.
If you are using shellclean() because it does something different, the best way to resolve your issue is to change the shellclean() function so that it returns single-quotes instead of double-quotes around the value. If you need help with that, post your shellclean() function declaration.
Edit: This post was recovered from search engine cache.
Re: Not escaping properly
Posted: Fri Apr 17, 2009 12:58 pm
by drschwartz
With your help, I got it figure out. Thanks so much!!!
David