Couple of questions on Prepared Statements

Question

Possible Duplicate:
why MySQLi prepared statements?

So I have a couple of questions on prepared statement.

First one is, my friend told me they were more secured than the traditional "Select from * users where sID = '$id';", something bout the database taking the input different and knowing that it's a value not a whole query so ;' didn't end the query.

Second, if you use prepared statements, do you still have to escape the values?

Third, do prepared statements make a big difference in performance/reliablity?

See the first answer on http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php — Michael Berkowski, Mar 16 '12 at 03:19
For the first one, an example [here](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain). — bfavaretto, Mar 16 '12 at 03:21
Many other equivalent questions -- [take your pick](http://stackoverflow.com/search?q=prepared+statements+still+escape) — Michael Berkowski, Mar 16 '12 at 03:23

score 2 · Answer 1 · answered Mar 16 '12 at 03:27

Escaping a query is necessary to distinguish values from commands:

SELECT * FROM foo WHERE name = 'O'Connor'

It's not readily comprehensible for the parser that the ' in "O'Connor" belongs to the value, it'll take it as terminating the string. Escaping helps the parser distinguish that:

SELECT * FROM foo WHERE name = 'O\'Connor'

Prepared statements are sent to the database in two steps: first the commands, then the values:

command: SELECT * FROM foo WHERE name = ?
value:   O'Connor

This makes it unambiguous for the parser. As a result, you do not need to escape the value, since the only reason you need to escape the value does not apply anymore. That's also why it's more secure, since you can never not escape the value and fall pray to SQL injection.

As for speed, it shouldn't change much for a single query. If you reuse the same prepared query several times with different values, it'll help improve performance since the database doesn't need to parse the whole query again and again.

If you want a more in-depth introduction: The Great Escapism (Or: What You Need To Know To Work With Text Within Text)

score 0 · Answer 2 · answered Mar 16 '12 at 03:21

0

Prepared SQL keeps SQL injection from occuring and can make code more readable.
Because they are prepared, they do not need prepared by you. So, no they do not need escaped
These will run just like any other TSQL statement. If you want performance gains, then you should look into using a stored procedure as they are "pre-compiled"

answered Mar 16 '12 at 03:21

Justin Pihony

66,056
18
147
180

I don't think 3 is entirely accurate. It's up to the implementation to determine whether to cache commonly used statements. – Chris Thompson Mar 16 '12 at 03:30
Yes, that is why I left it open, simply saying it will run just like any other TSQL statement. If the engine gets the same query again, then it will hit the cache, otherwise not. – Justin Pihony Mar 16 '12 at 03:35
right. Although the nuance to this is that if you use a standard string, it cannot hit the chache unless the exact same query is received. However, with a PS, if the parameters change, it will still be recognized – Chris Thompson Mar 16 '12 at 15:45

score 0 · Answer 3 · answered Mar 16 '12 at 03:22

Directly interpolating user input into an SQL statement is a huge security hole. It's called SQL injection. Using prepared statements will close that hole.
If you use binding, you don't have to escape the values yourself. Using prepared statements involves using binding, but you can use binding without using prepared statements.
Prepared statements improve performance if they are reused. For one-time use, they don't make much difference.

score 0 · Answer 4 · answered Mar 16 '12 at 03:22

First, yes, prepared statements, like parameterised statements, are more secure in their handling of parameters.

That's because the parameters are separated from the query itself. If you want to know what's wrong with just passing a query like:

select * from tbl where name = '$username'

just search the net for "Little Bobby Tables" :-) or "SQL injection".

Secondly, you shouldn't have to escape the values for a prepared statement, simply because the danger of SQL injection is removed (see previous point).

Thirdly, generally not. The performance comes down to the query being run against the database, which probably won't change that much between regular SQL and prepared statements.

score 0 · Answer 5 · answered Mar 16 '12 at 03:23

Prepared statements are pre-compiled, so the structure of the query is safe from tampering. Parameters are then bound to the statements and executed. If you are executing the same query - just with different arguments - many times prepared statements are in fact a huge performance increase. You don't need to escape the values if you're using a prepared statement. In short, prepared statements are great!

Couple of questions on Prepared Statements

5 Answers5