MySQL 8.4 Reference Manual Including MySQL NDB Cluster 8.4
Client applications that access MySQL should use the following guidelines to avoid interpreting external data incorrectly or exposing sensitive information.
Applications that access MySQL should not trust any data entered
by users, who can try to trick your code by entering special or
escaped character sequences in Web forms, URLs, or whatever
application you have built. Be sure that your application
remains secure if a user tries to perform SQL injection by
entering something like ; DROP DATABASE
mysql; into a form. This is an extreme example, but
large security leaks and data loss might occur as a result of
hackers using similar techniques, if you do not prepare for
them.
A common mistake is to protect only string data values. Remember
to check numeric data as well. If an application generates a
query such as SELECT * FROM table WHERE
ID=234 when a user enters the value
234, the user can enter the value
234 OR 1=1 to cause the application to
generate the query SELECT * FROM table WHERE ID=234 OR
1=1. As a result, the server retrieves every row in
the table. This exposes every row and causes excessive server
load. The simplest way to protect from this type of attack is to
use single quotation marks around the numeric constants:
SELECT * FROM table WHERE ID='234'. If the
user enters extra information, it all becomes part of the
string. In a numeric context, MySQL automatically converts this
string to a number and strips any trailing nonnumeric characters
from it.
Sometimes people think that if a database contains only publicly available data, it need not be protected. This is incorrect. Even if it is permissible to display any row in the database, you should still protect against denial of service attacks (for example, those that are based on the technique in the preceding paragraph that causes the server to waste resources). Otherwise, your server becomes unresponsive to legitimate users.
Checklist:
Enable strict SQL mode to tell the server to be more restrictive of what data values it accepts. See Section 7.1.11, “Server SQL Modes”.
Try to enter single and double quotation marks
(' and ") in all of
your Web forms. If you get any kind of MySQL error,
investigate the problem right away.
Try to modify dynamic URLs by adding %22
("), %23
(#), and %27
(') to them.
Try to modify data types in dynamic URLs from numeric to character types using the characters shown in the previous examples. Your application should be safe against these and similar attacks.
Try to enter characters, spaces, and special symbols rather than numbers in numeric fields. Your application should remove them before passing them to MySQL or else generate an error. Passing unchecked values to MySQL is very dangerous!
Check the size of data before passing it to MySQL.
Have your application connect to the database using a user name different from the one you use for administrative purposes. Do not give your applications any access privileges they do not need.
Many application programming interfaces provide a means of escaping special characters in data values. Properly used, this prevents application users from entering values that cause the application to generate statements that have a different effect than you intend:
MySQL SQL statements: Use SQL prepared statements and accept data values only by means of placeholders; see Section 15.5, “Prepared Statements”.
MySQL C API: Use the
mysql_real_escape_string_quote()
API call. Alternatively, use the C API prepared statement
interface and accept data values only by means of
placeholders; see
C API Prepared Statement Interface.
MySQL++: Use the escape and
quote modifiers for query streams.
PHP: Use either the mysqli or
pdo_mysql extensions, and not the older
ext/mysql extension. The preferred API's
support the improved MySQL authentication protocol and
passwords, as well as prepared statements with placeholders.
See also MySQL and PHP.
If the older ext/mysql extension must be
used, then for escaping use the
mysql_real_escape_string_quote()
function and not
mysql_escape_string() or
addslashes() because only
mysql_real_escape_string_quote()
is character set-aware; the other functions can be
“bypassed” when using (invalid) multibyte
character sets.
Perl DBI: Use placeholders or the quote()
method.
Java JDBC: Use a PreparedStatement object
and placeholders.
Other programming interfaces might have similar capabilities.
It is the application's responsibility to intercept errors that occur as a result of executing SQL statements with the MySQL database server and handle them appropriately.
The information returned in a MySQL error is not gratuitous
because that information is key in debugging MySQL using
applications. It would be nearly impossible, for example, to
debug a common 10-way join SELECT
statement without providing information regarding which
databases, tables, and other objects are involved with problems.
Thus, MySQL errors must sometimes necessarily contain references
to the names of those objects.
A simple but insecure approach for an application when it receives such an error from MySQL is to intercept it and display it verbatim to the client. However, revealing error information is a known application vulnerability type (CWE-209) and the application developer must ensure the application does not have this vulnerability.
For example, an application that displays a message such as this exposes both a database name and a table name to clients, which is information a client might attempt to exploit:
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist
Instead, the proper behavior for an application when it receives such an error from MySQL is to log appropriate information, including the error information, to a secure audit location only accessible to trusted personnel. The application can return something more generic such as “Internal Error” to the user.