I have heard that the PHP function mysql_escape_string has security vulnerabilities related to mult-byte characters. Are there any vulnerabilities if all tables are using Latin1 encoding?
The issue is that
In such a case, the attacker could append additional SQL commands to gain access to data or functions not intended. This is why the function is deprecated and the use of
Also note that
I believe it is ASCII, however I am unable to confirm this. Regardless it makes no difference to the result which is; using Latin1 for database encoding could result in a security vulnerability if not properly encoded.
Also note that this is not just vulnerability with the database. Within the application/server-side script you must be careful when handling input to be aware of how strings are passed from function to function and ensure that correct encoding is preserved. Ideally using UTF-8 end-to-end would be good but not always an option.
Per @BernieWhite's answer,
Therefore, it attempts to escape
But even if one uses
Because of these practical difficulties in ensuring that embedded string literals are handled safely, it is generally recommended that one not even attempt it! Instead, one can send literal values to the database connection in packets that are entirely separate from the SQL; consequently, the server will not even attempt to parse those values for anything else: this is known as "query parameterisation", is what @D.W.'s answer suggests, and is nicely explained in How can I prevent SQL injection in PHP?
Personally, I think you are asking the wrong question. If you want to avoid SQL injection vulnerabilities -- as well you should -- the answer is not to use
The fundamental problem is that
Personally, I take away a different lesson. The lesson I draw is that, if security is the job,
Among the security world, it is pretty widely accepted that the most robust way to avoid SQL injection is to use prepared statements. Don't try to escape/encode your data and then build up a SQL query using string concatenation; that approach is fragile and can easily break if, e.g., the database interprets your query differently than you expected. So my advice is: don't try to get clever -- just use prepared statements and be happy.