Disable any html in mysql column

Databases
1

I have a user-submission mysql which displays results on my page. The problem is that some nasty hackers submitted an iframe to a virus in the “name” field, which caused a lot of problems for me…

So, I want to disable people inserting iframes into my database. I really want this done from mysql perspective rather than php.

So, I want to have a column “name” which could only hold plain text elements. Any html tags would cause errors.

Is there a way to do this?

2

I know you don’t want to do it, but that’s something you check in PHP

3

You should be validating user input. If what is entered is not valid for a field then it should be rejected and the user asked to re-enter it long before you try to do anything with it such as saving it in a database.

$error = '';

if (validFieldX($_POST[fieldX']) {$fieldX = $POST['fieldX'];}
else {$error  .= 'fieldX is invalid<br>';}
// repeat above teo lines for all fields using an appropriate validation function

if ($error != '') {
// redisplay the page with the errors
} else {
// only now should you be doing anything with the data that was input
}
4

Database engines generally do not have ways of validating or removing HTML. The solution is simple: strip out HTML before you insert data into your database.

5

You can validate the input using javascript/jquery. The further you let these values travel towards your application backend the more vulnerable your application can become.

There are a couple of ways to do it:

  1. Use regular expression to check if name contains only alphabets or some other pattern
  2. Remove ‘<’ and ‘>’ or replace them with < and > before saving. These characters (<,>) cause the browser to treat mischievous form inputs as html elements. This way the browser will treat them as plain text instead even if you store it in your database.

For PHP based validation, you can check out htmlspecialchars() function:

6

Any JavaScript/jQuery validation MUST be repeated on the server. If you don’t then you effectively have no validation as anyone can turn off the client side validation.

7

thank you, I guess I will go with the php validation route