In sounds like we’re debating theory vs practice. In theory, a user might, some how, some way, find a way to leak through the escaping. But in practice, I don’t see any evidence of that. I think we have our bases covered.
This is getting a bit paranoid. We could apply this logic to everything in PHP. How do we know that function X was implemented correctly?
In that case why has PHP taken the first of two steps to prevent you from being able to use calls that jumble the code and data together by removing the interface that doesn’t allow you to keep them separate?
Why jumble code and data together when you don’t have to? Where there is a language construct that keeps them separate you completely eliminate all possibility of data being processed as code if you use that construct.
In practice jumbling them together is a mess and escaping the data is a patch. Keeping them separate is tidier and removes reliance on you remembering to escape data in order to patch it to work with the mess.
PHP is removing the mysql_ interface because it doesn’t support ways to keep the code and data separate. Both of the replacements - mysqli_ and PDO - recommend using prepare/bind rather than query - presumably the option to use query and to jumble the code and data together will be removed in a later version and is only supported now to make it easier to migrate from a jumbled mess of code and data to keeping them completely separate by doing it as two steps instead of all at once.
Each to their own, to me a query with data looks cleaner than a query with placeholders plus a set of data as a separate entity. And I don’t think escaping was ever done as a patch - from what I know prepared statements were created to improve the performance of repeated queries with different sets of data.
If I were to make the prediction this will never happen! 
Don’t forget SQL is a language and has to be easy to be understood by humans. It’s as if you said that future versions of PHP won’t allow to jumble data (literal string, numbers, etc.) with the code (statements, constructs, functions, etc.) and instead each script will require two separate files - one with the code (and placeholders) and the other with data!
I actually agree with you here. As I said, I favor prepared statements. The only point that people were taking issue with was the claim that escaping is insecure, based on some hypothetical vulnerability that doesn’t seem to exist in practice. I agree that prepared statements seem tidier, but I don’t agree that escaping is insecure.
I never said that escaping is insecure. I said that using an approach that requires escaping is less secure than one that is inherently secure.
With prepare/bind you do not need to look beyond the prepare statement to know that it is secure. With an approach that requires escaping you need to look beyond the database call and into the PHP code to know whether the call is secure or not - that has the potential to be less secure simply because there is more code you need to look at to check security.
PHP has always been able to produce completely secure code but most people using it do not learn about security when they learn how to write code and so have in many cases used code in ways that have the potential to be less secure. As a result those responsible for PHP are gradually removing those ways of coding that have the greatest potential for being misused to produce less secure - hence register globals got turned off by default and then completely deleted and similarly the mysql_ interface is being removed. PHP is being made into a more secure language by removing the ways that insecure code could be produced.
Jumbling code and data together is less secure than keeping them separate BECAUSE it requires that any data that could be misread as code be escaped. Using real_escape_string is less secure because you might forget to apply it to a field that needs it. Yes there is a really slight possibility of it containing a security hole that we can disregard as if such a hole ever gets found then it will get fixed for us - leaving it off a field by accident is a far higher possibility and that’s probably the one that makes the biggest difference - particularly in a medium to large application where you can’t just apply it to all database fields and slow the processing down for numbers and dates and other things that cannot possibly contain anything that could possibly be mistaken for code - where the person responsible for optimising the code will strip out the escaping of those fields where escaping is not needed so as to speed up the processing by hopefully enough to make the application useable.
Escaping everything whether it needs to be escaped or not is only really suitable for tiny applications with only a few hundred users. Once you have several million users all trying to make database calls at the same time you will need to have the processing run as efficiently as possible and unnecessary calls such as escaping dates will get stripped out.
Quick aside. With prepared statements:
If you don’t validate your data and you pass ‘345regdfgtr’ as an int, does the query just fail?
If you get the type wrong, (e.g. s for a double) does the query just fail too? (I.e. no injection)
I am going to try this for myself when I get the chance but thought I’d ask.
Regarding the main discussion, I agree this is a matter of preference and perhaps you could use standard queries and prepareds side-by-side and get the best of both world (i.e. standard querys when no user input and you’re only running it once, prepared for everything else).
I for one put all user input through a cleaning function and have to explicitly pass false to not escape it. There’s still a chance of injection through my error, for sure, but then every line of code I write could contain an error.
No, it will not fail but the value will be converted to a number based on some “best guess” mechanism. You’d have to test it yourself because I can’t remember the exact behaviour when I did such tests some time ago. Most probably your sample string will be converted to number 345, and if the string begins with a non-digit then it will be converted to 0.
No, the query will never fail and you are always safe from injections. The only bad thing that can happen is that the value might get converted to a different one. Generally, you could get away with always setting the type to string since this would be equivalent to values in single quotes in SQL and mysql permits them. But as I said earlier, there can be some edge cases like very big numbers when the type you set might influence the value used by the query so to be 100% safe I’d set the types properly.
Do you mean you put every input variable through real_escape_string? While filtering and sanitizing data is okay, I think using real_escape_string on all input by default is not a good idea because I think responsibility for escaping data lies in the code that actually constructs the query, not in the code which receives the input and validates it. If you have all input data escaped from the start then you can only use the data in an sql query. Suppose later you want to add some more code that will additionally send some of the input data via email or store in an XML file - then you’d have to unescape the data to get rid of all escape characters that are just junk for the other purposes. This is one of the reason why magic_quotes were removed from PHP, which essentially did the same thing - they auto-escaped all input data.
Only if they’re strings and going into the database. If they’re going into an email, for example, I would set escaping to false to avoid the extra characters. I understand what you are saying and fully agree.