[PHP] FORM -> SQL -> HTML

How do >>>you<<< do it?

[dmorison]

Ok, i'm not asking for an answer here, but i'm interested in how other PHP users take the various precautions that you must take when writing a [web based] database application.

For example, some 31337 haXOr thought it would be funny to enter the following as his "name" on your registration form:

\i<am>31337'est\\haX0r

Your application is required to take that name, store it in a database, and display it in the future as part of a dynamic web page.

There are three things in the name this 31337 haX0r gave us that could upset us.

Firstly, the <am>, which if not handled appropriately would be interpreted by a browser as a tag, and not displayed.

Secondly, the apostrophe on the end of 31337, which if not handled appropriately could upset your creation of an SQL query for presentation to your database.

and thirdly, the collection of backslashes, which if not handled appropriately could get lost in magic quoting functionality!

So.... How do >>>YOU<<< do it?

[ruserious]

1)magic quotes off
2)htmlspecialchars()
3)str_replace("\'","''", ) //Replace slashed quote with two single quotes for ANSI-conformity
4)Now either insert/update to the DB or echo to the browser
5) When selecting from the DB, stripslashes is not required. So selecting and direct echo'ing is ok.

[daisho]

Well you have 2 issues here.

1. Getting the data into the database correctly
2. Displaying the data correctly

The first problem depends on the database you are using.

MySQL-> use mysql_escape_string. This will properly escape the sting to ensure that the "'" (single quote) and many much much worse security holes are handled.

PEAR-> DB::Quote() will do the same as mysql_escape_string. You can also look at DB::Prepare() and DB::Execute() member functions of PEAR. Check out "pear dot php dot net" for more info.

Oracle-> Use binds. OCIParse, OCIBindByName or OCIBindByNum and OCIExecute.

Since every database interface is different in PHP (Which is what the PEAR Library tries to solve) you may need to follow a different procedure if your database is different. Or simply use PEAR.

The second problem is an output problem. You will want to look at the PHP function htmlentities which will encode all special HTML characters so the browser will display them as the character rather than possibly trying to parse them as HTML.

[dmorison]

ruserious,

Thanks - this is the kind of thing I'm getting at - there of course being more than one way to skin a cat.

Am I correct in thinking that your steps described above result in the database storing HTML safe code...?

For example,

"i<am>"

is stored in your DB as "i&lt;am&gt;"

[dmorison]

I tend to use the sequence of events daisho described, and magic quotes off as ruserious points out.

Before building a query, I use the following on bog standard 255 max character text fields:

function dbSafeVarchar($s)
{
return substr(mysql_escape_string($s),0,255);
}

So, for example, if $name came in off a form:

$name = dbSafeVarchar($name);

You're then safe to build an SQL string containing '$name'.

I then perform HTMLizing the code on the way out - main reason being that I use the database contents for other purposes - such as accounting.

On recovering from the database:

echo "<p>".htmlentities($name,ENT_QUOTES)."</p>";

ENT_QUOTES is required incase you were to render a database field into the value='' of an HTML form field. If not, your subsequent re-submission could become chopped at the position of the first apostrophe within the output.

All good fun!

[grahamstewart]

For things like a username you can always just use a regular expression to make sure that it only contains alphanumerics and underscores. If it doesn't then just reject it.