A few questions about PDO and prepared statements

Question

I'm starting to use PDO and prepared statements in my applications, but i have some questions to the pros out there. Hope you can help me! :)

1. When should i use prepared statements? Every query in my entire application?
2. Can i use prepared statements with INSERT's?
3. Can i use prepared statements with variable columns in my INSERT?
4. How much faster prepared statements are (when using SELECT or INSERT)
5. Can i use prepared statements with UPDATE?
6. Why should i use them, other than speed improvement and security?

Answer 1

When should i use prepared statements? Every query in my entire application?

Yes. Use them everywhere. There are few edge cases where you would still need concatenated SQL and value escaping. Bound parameters cannot replace dynamic SQL construction.

Can i use prepared statements with INSERT's?

Yes. INSERT INTO tbl (x,y,z) VALUES (?,?,?)

Can i use prepared statements with variable columns in my INSERT?

Yes. But column names cannot be bound parameters. Constructing dynamic INSERT statements needs a filter and/or escaping function.

How much faster prepared statements are (when using SELECT or INSERT)

There is no general answer.

Speed gains occur if you loop over data arrays and reuse a prepared statement to insert. Ordinary queries: depends. Test yourself.

Can i use prepared statements with UPDATE?

Yes. UPDATE tbl SET x = ? AND y = ? WHERE z = ?

Why should i use them, other than speed improvement and security?

Makes SQL queries more readable.