CODEWITHSUNDEEP
phpmysqlmysqli
SM9
SM9

Reputation: 111

PHP MySQL query failing in some cases?

I have a PHP script which receives post requests to enter clients into a database, it works most of the time but in some cases it fails if the client has special characters in his name, What makes it even more weird is if I try running the query manually in PHPMyAdmin it works fine, the php code I am using is here:

$mysql = new mysqli("***", "****", "****", "****");
mysqli_set_charset($mysql,"utf8mb4");

if (isset($_POST["name"])) {
$name = $mysql->real_escape_string($_POST["name"]);
} else {
mysqli_close($mysql);
die("Name not set");
}

$query = sprintf ("INSERT INTO `que` (`name`, `ip`, `steamid`, `serverip`) VALUES ('%s', '%s', '%s', '%s')", $name, $clientip, $steamid, $serverip.":".$serverport);

if(!$mysql->query($query)) {
    mysqli_close($mysql);
    die("Query Failure: " . $query);
}

As stated, the above code works 90% of the time, but it some cases (I believe caused by special characters in the name) it will fail.

Here are some cases of failure:

INSERT INTO `que` (`name`, `ip`, `steamid`, `serverip`) VALUES ('Shrimp%20ᴳᵀ%20ƒ„%20s%20%20t', '****', 'STEAM_1:0:16045698', '178.32.48.195:27015')
INSERT INTO `que` (`name`, `ip`, `steamid`, `serverip`) VALUES ('★★%20—%20noichia%20—%20…★', '****', 'STEAM_1:0:103227484', '178.32.48.195:27015')
INSERT INTO `que` (`name`, `ip`, `steamid`, `serverip`) VALUES ('•%20rie%20Low%20¼rke%20¢%20Ù„', '*****', 'STEAM_1:1:60346821', '178.32.48.195:27015')

All the post inputs are sanitized with the following (Not that somebody can post bad things anyway since only my servers are able to post):

$mysql->real_escape_string

Is there something I am doing wrong?

Any help is much appreciated!

Edit: I also URL encode before I send the request to the PHP using the following: 

stock void URLEncode(char[] str, int len)
{
char[] str2 = new char[len * 3 + 1];
Format(str2, len * 3 + 1, "%s", str);

char ReplaceThis[20][] =  { "%", " ", "!", "*", "'", "(", ")", ";", ":", "@", "&", "=", "+", "$", ",", "/", "?", "#", "[", "]" };
char ReplaceWith[20][] =  { "%25", "%20", "%21", "%2A", "%27", "%28", "%29", "%3B", "%3A", "%40", "%26", "%3D", "%2B", "%24", "%2C", "%2F", "%3F", "%23", "%5B", "%5D" };

for (int x = 0; x < 20; x++) {
    ReplaceString(str2, len, ReplaceThis[x], ReplaceWith[x]);
}

if (strlen(str2) > len * 3) {
    LogError("statistics encode url exceeded length (%d): %s", len * 3, str2);
}

Format(str, len, "%s", str2);

if (strlen(str) > len - 1) {
    LogError("statistics encode url exceeded length (%d): %s", len, str);
}
}

Upvotes: 1

Views: 161

Answers (1)

e4c5
e4c5

Reputation: 53734

As others have pointed out in the comment, this is much easier if you use prepared statements. If you need to insert multiple records per view it could be faster too.

Here's how your code can be changed.

$mysql = new mysqli("***", "****", "****", "****");
mysqli_set_charset($mysql,"utf8mb4");

if (isset($_POST["name"])) {

    $query = $mysql->prepare("INSERT INTO `que` (`name`, `ip`, `steamid`, `serverip`) VALUES (?, ?, ?, ?)")
    $query->bind_param("ssss", $name, $clientip, $steamid, $serverip.":".$serverport);

    if($query->execute()) {
        print 'ok';
    }

} else {

   print 'Sorry required data not found';
}

It's also worth mentioning that you don't need to explicitly close the connection.

Upvotes: 1

Related Questions