Sanitizing input

Our textbook tells you how; let me offer some perspective on when to use which method.

If you have a query with no variables, you are safe.  Example:  "SELECT * FROM users WHERE id=1".

If not, there are two things I know of that can go wrong:

You can get an SQL injection.  For example, if (say) $username = "Robert'); DROP TABLE students; --", and your query is "SELECT * FROM users WHERE (username='$username')", then this is what you'll be sending to MySQL:

SELECT * FROM users WHERE (username='Robert'); DROP TABLE students; --')

And you've just erased a big chunk of your database.  Ouch.

 

Solution:  prepared statements.  If you do prepared statements, you won't need to sanitize your inputs to protect your queries.  (Or so I hear.  If I'm wrong, blame, well, somebody who isn't me.) 

You can get an HTML injection.  Suppose instead Robert's name is typed in as "<a href="http://linktoillegalpharmacy.com"><img src="offensivePicture.jpg></a>".  It won't trash the database, but when you list student names, you get...that.

Even worse is if Robert's name is typed in as Javascript code to mail everyone this ad.

Solution:  sanitize for HTML injection using htmlentities.

When do you need to be concerned?  Any time you might send input to MySQL or to the browser.  Whether that input comes from something a user typed, or $_POST, or $_COOKIE, or $_GET (it's really easy to forge URL arguments!).  If you're about to encrypt it because it's a password, I don't see a problem.  If you're only going to compare it to something (say, if ($_GET['action']='checkout)...), I don't see a problem.  But if it's part of a query, do a prepared statement; if it's going into the database (say, with INSERT) or might be printed, sanitize it.