If I remember correctly, I think Jeff has mentioned in the Stack Overflow podcast a possible weakness in SQL prepared statements. I'm wondering what kind(s) of weakness(es) did he refer to? Was it possibly just about inappropriate usage thereof, or something more sinister?
The podcast, to my remembering, didn't go deeper into the subject, it was just a pass-by-remark.
I haven't listened to the podcast, but in my experience only good comes from prepared statements. It often improves the performance of the application and prevents SQL injection (if used right, not as the second example in your link).
If the statement that is prepared has the parameters dynamically constructed in any way, then that would more than likely be the source of the weakness.
If you use a proper database library with tested classes for setting parameters, then you don't open yourself up to a SQL injection at any point, prepared statement or not.
Remember, just because a statement is prepared, or because you are using a stored procedure, it doesn't mean that you are safe from injection attacks. It is only when you are using database provider code which will sanitize the input of parameters (as well as applying it to all parameters that can be used) that you gain protection from SQL injection.
Beyond the normal sql injection (what we might call first order) attack there are secondary levels. For example its not uncommon to have stored procedures use string concatenation to build a query which it then executes. If result of retrieved field values are included in such a concatenation then there is a danger of injection.
I think what he said was that, when you use Prepared Statements, SQL server could cache your query execution plan, so, even if you modify some of the parameters on the executing query, the server could pick the wrong (probably cached) execution plan that would perform very badly.
He also mentioned a new feature of SQL Server 2008 to force the engine to re-evaluate execution plans that he used to overcome this situation.
With Prepared Statements, the only issue I have is this. Consider the following Java Code:
String sql = "select * from table where name like ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "PATTERN%");
ResultSet rs = pstmt.executeQuery();
Here you would expect that, if you have an index on table(name) it will be used by the query plan. Well, it won't. Because PraparedStatement must precompile and expect the worst: '%PATTERN%', for example. So, it won't optimize. It took me a while to figure this one out. It was causing my database to suffer. :(
Hope it helps.