Security flaws in smartphone application

Question

And could you give me any security and optimization recommendations?

<?php

$datvariable = $_POST['comingover']; 

$datvariable = htmlspecialchars($datvariable);

$datvariable = cleanPost($datvariable);

function cleanPost($item) 
{ return mysql_real_escape_string(strip_tags(stripslashes($item))) }

$mysqliobject = new mysqli( 'localhost', 'myusername', 'mypassword', 'mydatabase');

if( mysqli_connect_errno( ) )   {   echo "Central SQL Access Malfunction";  exit(); }

$uploadquery = $mysqliobject->prepare( 
"INSERT INTO ENTIRETABLE ( WORD, SOUNDCLICKCOUNT ) VALUES (?,'0') 
      ON DUPLICATE KEY UPDATE SOUNDCLICKCOUNT = SOUNDCLICKCOUNT+1"
                                        );

$uploadquery->bind_param('s', $datvariable);

$uploadquery->execute();

$mysqliobject->close();

?>

I do not see any fatal error for now.

I plan to have a database that has about 10 tables, and each table will contain about 500 records. Each column will have 50 characters at max. Is MySQL good enough for that purpose? It is for a smartphone application that helps students in East Asia learn the English language.

Answer 1

mysql_real_escape_string

mysql_real_escape_string is deprecated and thus should not be used anymore. As you are using mysqli an alternative would be mysqli_real_escape_string.

But you don't need it anyways, because you are already using prepared statements (which is generally recommended over using any of the real_escape_string functions).

So you can just get rid of cleanPost, it doesn't add any security against SQL injections that the rest of your code doesn't already take care of.

htmlspecialchars

htmlspecialchars is for encoding HTML characters, and used to prevent XSS attacks. You don't need it (and shouldn't use it) when adding data to the database, but when printing user supplied data to a user.

So you should also get rid of the call to htmlspecialchars.

Misc

• you are missing a semicolon at the end of your return statement.
• you shouldn't just exit, throw an exception instead (so the calling code can decide what to do about it).
• Yes, I would assume that MySQL can handle that sort of data on any computer without a problem.