0

What sanitisation should be done to form inputs before inserting into a MySQL database?

I have just 2 user-entered inputs in my PHP web application which will be sent to a series of successive pages (both via GET and via POST), before they are eventually stored into a MySQL database.

Both are text inputs (e.g., <input type="text />"). The first is a field which should be filled by a Google maps api javascript, but which I nevertheless obviously can't trust; the second is a freely entered username field, which at the moment only has a restriction on the string length.

What server-side checks or transformations should I perform on the values of these two fields before inserting them into the database, to prevent malicious actions, or other errors perhaps with special characters?

I am using PDO prepared statements for all my database operations, like:

$id = '1234567';
$username = 'John Smith'; //Could be weird characters like "Seán Óg Ó hAilpín"!
$Query = $db->prepare("INSERT INTO `MyTable` (`id`, `username`) VALUES (?,?)");
$Query->execute(array($id, $username));

If the values should be encoded or transformed, how should they be unencoded before displaying in HTML?

(All my other form variables are the result of choices from drop-down select menus, and I thought of validating & sanitising their values by simply exiting the receiving script if any of them was not (!===) any of the limited number of variables I am expecting. I'm hoping this 'whitelisting' method would be sufficient for those.)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
imagina
  • 65
  • 1
  • 6
  • "If the values should be encoded or transformed, how should they be unencoded before displaying in HTML?" — This is a [different question](https://stackoverflow.com/questions/1996122/how-to-prevent-xss-with-html-php) to the one you asked in the question title. Databases and HTML are not the same thing. – Quentin Apr 09 '18 at 13:29
  • Isn't the "possible duplicate" simply recommending PDO & prepared statements? – imagina Apr 09 '18 at 13:31
  • Yes, that's all you need to do to data before inserting it into a database. – Quentin Apr 09 '18 at 13:33
  • @Quentin - so the values can get put in to the DB in any form using that method, without problem? – imagina Apr 09 '18 at 13:33
  • That's the point of bound parameters. – Quentin Apr 09 '18 at 13:34

0 Answers0