36.2. Security-Related SQL Mode Values


Many operational characteristics of MySQL Server can be configured by setting the SQL mode. This mode consists of option values that each control some aspect of query processing or other server behavior. (See Chapter 1, "Client/Server Concepts, " for background information on the SQL mode and how to control it.)

New SQL mode values are implemented from time to time. To determine whether a new release of MySQL includes new SQL mode values, check the upgrade notes in the installation chapter of the MySQL Reference Manual. By default, new mode values are not enabled by default as part of your server's SQL mode. However, if you decide that a given value should be part of the standard mode used by your server, you can enable that value with the --sql-mode startup option. (See Section 24.8, "Setting the Default SQL Mode.") For example, TRADITIONAL mode is new in MySQL 5. You can run the server in that mode by putting the following lines in an option file:

 [mysqld] sql-mode=TRADITIONAL 

The following discussion lists examples of security-related SQL mode values that are new in MySQL 5, along with an explanation of why you might want to enable them for your server.

Additional information about these modes can be found in Section 5.8, "Handling Missing or Invalid Data Values."

  • Several SQL mode values relate to data security. That is, they protect against data corruption through inadvertent or willful attempts at entering invalid data into tables:

    • Strict mode enables general input value restrictions. In strict mode, the server rejects values that are out of range, that have an incorrect data type, or that are missing for columns that have no default. Strict mode is enabled using the STRICT_ALL_TABLES and STRICT_TRANS_TABLES mode values.

    • Division by zero can be treated as an error for data entry by enabling the ERROR_FOR_DIVISION_BY_ZERO mode value and strict mode. In this case, attempts to enter data via INSERT or UPDATE statements produce an error if an expression includes division by zero. (With ERROR_FOR_DIVISION_BY_ZERO but not strict mode, division by zero results in a value of NULL and a warning, not an error.)

    • Several SQL mode values control how MySQL handles invalid date input. By default, MySQL requires that the month and day values correspond to an actual legal date, except that it allows "zero" dates ('0000-00-00') and dates that have zero parts ('2009-12-00', '2009-00-01'). Zero dates and dates with zero parts are allowed, even in strict mode. To prohibit such dates, enable strict mode and the NO_ZERO_DATE and NO_ZERO_IN_DATE mode values.

    • The TRADITIONAL mode value is a composite mode that enables strict mode as well as the other restrictions just described. If you want your MySQL server to be as restrictive as possible about input data checking (and thus to act like other "traditional" database servers), the simplest way to achieve this is to enable TRADITIONAL mode rather than a list of individual more-specific modes. Specifying TRADITIONAL has the additional advantage that if future versions of MySQL implement other input data restrictions that become part of trADITIONAL mode, you won't have to change the server configuration to enable those modes to take advantage of them.

  • The GRANT statement assigns privileges to an account, and it also creates the account if it does not already exist. However, implicit account creation might be a problem if you neglect to include an IDENTIFIED BY clause: In that case, GRANT creates a new account that has no password and thus is insecure.

    If you want to prevent the GRANT statement from creating new accounts unless an IDENTIFIED BY clause is given, enable the NO_AUTO_CREATE_USER SQL mode. Then, when you issue GRANT statements, you won't accidentally cause new accounts that have no password to spring into existence.

Several SQL mode values control how MySQL handles invalid date input. By default, MySQL requires that the month and day values correspond to an actual legal date, except that it allows "zero" dates ('0000-00-00') and dates that have zero parts ('2009-12-00', '2009-00-01'). Zero dates and dates with zero parts are allowed, even in strict mode. To prohibit such dates, enable strict mode and the NO_ZERO_DATE and NO_ZERO_IN_DATE mode values.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net