Cleansed, folded and manipulated.

October 3, 2012 8:28 AM

Beginner at PHP: Inserting form data into MySQL. I need to strip special characters out, for security and MySQL compatibility, but the options are overwhelming me and I've spent far longer on this issue than the whole project so far.

I'm attempting to write a small web app that will take maintenance and IT service requests, distribute them to the proper people and organize them in a way that makes sense to us (I've looked for a suitable solution that is already built but they all have problems that make them not work for us).

The data for the requests is placed into an array and then inserted via INSERT to the database. I know that I need to sanitize the input to keep bad people out and also to keep MySQL from throwing errors, but I've found that this is a surprisingly difficult and vast subject when starting with PHP. I've tried SEVERAL options but as I am a novice, the errors I keep getting are not easily sussed out without a more experienced someone to bounce things off of and I haven't been able to make it work by reading random bits on the web. Can someone help explain what I would need to include for a simple $_POST array to be cleansed and for it to be made human readable when retrieved and displayed in a list (i.e. apostrophes, slashes, etc back to their proper spots)?

Extra points for a good beginner's forum that doesn't mind these sort of elementary questions? I've had a hard time finding people that will be bothered with a neophyte without smothering said neophyte with stuff that requires more advanced knowledge.

posted by dozo to Computers & Internet (9 answers total)

See mysql_real_escape_string(). This is what it was designed for.

stack overflow usually accepts novice-level questions, as long as they're written clearly.
posted by zug at 8:31 AM on October 3, 2012

An example:

$myvar = mysql_real_escape_string($_POST['id']);

will save a sanitized version of $_POST['id'] that you can then safely put into a MySQL query.
posted by zug at 8:33 AM on October 3, 2012

Use either PHP's mysqli or (preferably) PHP's PDO interface rather than the raw mysql interface, and use parameterized queries to do this for you. You should absolutely not be trying to sanitize database input (and output) yourself as a beginner. Rely on the libraries that are designed to handle this. Using parameterized queries is a best practice everywhere you go, so this is really a worthwhile step to take.

For tutorial, look in the PHP documentation for them. They provide working examples, and the comments on each page usually go much further with working code.
posted by fatbird at 8:47 AM on October 3, 2012 [3 favorites]

I agree with fatbird, since you're just starting out and it sounds like a new project you should use PDO if its available on your server. There are less tutorials for it and debugging queries isn't as nice and simple as "or die(mysql_error())" but it does all the data sanitization for you.
posted by missmagenta at 8:57 AM on October 3, 2012

nthing fatbird: get into the habit of using prepared statements now, and however long it takes will spare you many times the grief along the line.
posted by holgate at 1:26 PM on October 3, 2012

Agreed with above suggestions that you should be using some support code that helps with this. Don't write a site in plain PHP, find a framework suitable for what you want to do that solves some of these problems for you. At a very minimum, use PDO, but perhaps learn about Model/View/Controller architectures and use something like CodeIgnitor/Kohana.

There are two things to worry about, by the way: SQL injection in DB queries, and XSS/HTML exploits when you display user-entered information to other users of your site.
posted by myotheraccount at 11:01 PM on October 3, 2012

In line with what myotheraccount said, I always run user input through strip_tags unless it's explicitly a field allowing HTML.
posted by fatbird at 11:05 PM on October 3, 2012

I know that I need to sanitize the input to keep bad people out and also to keep MySQL from throwing errors

Bzzzzt. Stop there. If you need to sanitize stuff to protect your own database (as opposed to protecting your web clients who end up pulling stuff back out of that database) you're doing it wrong.

Doing it wrong is amazingly popular, probably because gluing SQL queries together from snippets is the way most people first learn how to build them. But it's wrong all the same.

If you use prepared statements properly, nothing user-supplied ever actually makes it into the body of the SQL query itself: a strict separation is maintained between the query and the data items passed through it, which means that stuff inside the data that looks like SQL never gets the chance to act like SQL.

It's well worth wrapping your mind around this particular piece of "advanced knowledge", because there are just so many ways that "sanitization" can fail.
posted by flabdablet at 1:24 AM on October 4, 2012

Response by poster: Thanks, everyone. Although I considered PDO etc a little overkill for this basic usage, due to this thread I'm realizing that it'd be in my best interest to put on my big boy pants and learn it the right way.

I really appreciate the suggestion for a framework, too. I've been using notepad++ but anything that holds my hand a little would probably hep immensely.

Again, thanks!
posted by dozo at 6:55 AM on October 5, 2012