How to handle (on the server side) ampersand while inserting text into MySQL using PHP?

When we insert a string with ampersand character into mysql, the string is inserted only till & character is encountered.

Example: when inserting "one & two"... only "one " is inserted.

Tried a few things like... htmlentities OR htmlspecialchars... nothing working for me.

Current PHP code:

$sms = htmlentities($_POST["sms"]);


$query = "I开发者_开发问答NSERT INTO sms (detail,catID,deviceID) VALUES ('".$sms."',".$_POST["catID"].",'".$_POST["deviceID"]."')";

And as the app is live... I need to resolve it by editing the PHP files only, on the server. (I hope it can be done)

---

Here is a secure (against SQL injection) query:

$query = sprintf("INSERT INTO sms (detail,catID,deviceID) VALUES ('%s', '%s', '%s')",
                 mysql_real_escape_string($_POST['sms']),
                 mysql_real_escape_string($_POST['catID']),
                 mysql_real_escape_string($_POST['deviceID']));

I am escaping everything as a string, because I don't know your table schema.

However, if catID and deviceID are INTEGER types, then it should be:

$query = sprintf("INSERT INTO sms (detail,catID,deviceID) VALUES ('%s', %d, %d)",
                 mysql_real_escape_string($_POST['sms']),
                 ((int) $_POST['catID']),
                 ((int) $_POST['deviceID']));

---

htmlentities(); function is best used when displaying data from the database or a POST to protect the user and page form XSS

When inserting into the database sanitize to protect the database from abuse, using mysql_real_escape_string():

if(isset($_POST)){
    foreach($_POST as $key=>$value){
        if(get_magic_quotes_gpc()) {
            $value=stripslashes($value);
        }
        $_POST[$key]=mysql_real_escape_string($value);
    }
}

$query = "INSERT INTO sms (detail,catID,deviceID) VALUES ('".$sms."',".$_POST["catID"].",'".$_POST["deviceID"]."')";

---

Ok, well to complete the discussion, couldn't found a server side only solution... requires modification at both client & server side.

Here's how I needed to pass the upload text (as newStr):

    NSMutableURLRequest *request = [NSMutableURLRequest requestWithURL:url cachePolicy:NSURLRequestReloadIgnoringCacheData timeoutInterval:30];
[request setHTTPMethod:@"POST"];

NSString *newStr = (NSString *)CFURLCreateStringByAddingPercentEscapes(
                                                                               NULL,
                                                                               (CFStringRef)uploadText.text,
                                                                               NULL,
                                                                               (CFStringRef)@"&",
                                                                               kCFStringEncodingUTF8 );

NSString *parameters = [[NSString alloc] initWithFormat:@"sms=%@&catID=%d&deviceID=%@",newStr,webCatID,[[UIDevice currentDevice] uniqueIdentifier]];

[newStr release];

And this modification on the server side:

    if ( get_magic_quotes_gpc() )
{
    $_POST['sms'] = stripslashes($_POST['sms'] );
}

$query = sprintf("INSERT INTO sms (detail,catID,deviceID) VALUES ('%s', '%d', '%s')",
                 mysql_real_escape_string($_POST['sms']),
                 ((int) $_POST['catID']),
                 mysql_real_escape_string($_POST['deviceID']));

Special thx to @PhpMyCoder & @Shef

& LOL on the urgency!