MySQL - When I have to escape variables and when not?

I'am wondering if it's necessary to escape internal variables, which came from the data开发者_Go百科base itself and are an user-input. Is it just a waste of performance or is it more secure?

Thanks!

You don't need to worry about that. If a value in the database has the name of anything reserved, it won't matter as it won't be interpreted.

You should worry about sql injection and make sure that any statements that you run that insert, delete, select or update data from user input escape single quotes and other characters. Even regular users - with no bad intentions - that will use single quotes will get errors which is bad.

If a user uses a user name like:

joe'; drop table users; --

You may be in trouble.

You must escape all user input, which even includes things like server variables. If a value is pulled from the database and not touched in any way by user input, it needn't be escaped when reused in a query unless it contains characters that would break that query when passed in by string concatenation.

For example, I pull the name O'Brien from the database into a PHP variable $name and then in another statement attempt to put it back in like this:

$query = "INSERT INTO table (id, name) VALUES (123, '$name');";

This will break the query, as I have failed to escape the ' in O'Brien.