Not escaping properly

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
drschwartz
Forum Newbie
Posts: 12
Joined: Fri Feb 27, 2009 8:49 am

Not escaping properly

Post 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
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Not escaping properly

Post 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().

Code: Select all

die('{'.$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.
Last edited by McInfo on Mon Jun 14, 2010 2:44 pm, edited 1 time in total.
drschwartz
Forum Newbie
Posts: 12
Joined: Fri Feb 27, 2009 8:49 am

Re: Not escaping properly

Post 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
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Not escaping properly

Post 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

Code: Select all

die('{'.$sql.'}');
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.
Last edited by McInfo on Mon Jun 14, 2010 2:46 pm, edited 1 time in total.
drschwartz
Forum Newbie
Posts: 12
Joined: Fri Feb 27, 2009 8:49 am

Re: Not escaping properly

Post 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!! :D

Thanks again for your help,
David
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Not escaping properly

Post by McInfo »

If you use

Code: Select all

die('{'.$sql.'}');
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.
Last edited by McInfo on Mon Jun 14, 2010 2:48 pm, edited 1 time in total.
drschwartz
Forum Newbie
Posts: 12
Joined: Fri Feb 27, 2009 8:49 am

Re: Not escaping properly

Post 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
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Not escaping properly

Post 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.
Last edited by McInfo on Mon Jun 14, 2010 2:49 pm, edited 1 time in total.
drschwartz
Forum Newbie
Posts: 12
Joined: Fri Feb 27, 2009 8:49 am

Re: Not escaping properly

Post by drschwartz »

With your help, I got it figure out. Thanks so much!!!

David
Post Reply