Find solution
Will there be memory problems between multiple web service requests?

Cleansed, folded and manipulated.

  • 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.

  • Answer:

    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.

dozo at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

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.

zug

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.

zug

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.

missmagenta

nthing fatbird: get into the habit of using http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php now, and however long it takes will spare you many times the grief along the line.

holgate

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.

myotheraccount

In line with what myotheraccount said, I always run user input through strip_tags unless it's explicitly a field allowing HTML.

fatbird

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 http://php.net/manual/en/pdo.prepared-statements.php 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 http://xkcd.com/327/ 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.

flabdablet

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!

dozo

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.