Shulou(Shulou.com)06/01 Report--

Inception SQL audit comments

1. Build a table sentence

Table statement check item

Check items for table properties

This table does not exist

For create table like, it checks whether the old table of like exists.

For create table db.table, it checks whether the database db exists.

The length of table name, column name and index name is no more than 64 bytes

If you are creating a temporary table, you must prefix it with tmp

You must specify the storage engine that establishes the innodb (configurable)

You must specify the character set of the utf8 (the string is configurable to specify which character sets are supported)

Tables must be annotated (configurable)

Table cannot be established as a partition table (configurable)

There can only be one self-increasing column

Index name cannot be Primay

Foreign key is not supported (configurable)

When creating a table, if the specified value of auto_increment is not 1, an error is reported (configurable)

If the name of the self-increment column is not id, it may be meaningful. It is dangerous to use MySQL in this way, so alarm (configurable)

Check items for column properties

Cannot set character set for column (configurable)

Column types cannot use collection, enumeration, or bitmap types. (configurable)

Columns must be annotated (configurable)

When the length of char is greater than 20, it needs to be changed to varchar (the length is configurable)

Column cannot be of type BLOB/TEXT. (configurable)

Use not null for each column (configurable)

If the column is of type BLOB/TEXT, this column cannot be set to NOT NULL.

How to add a column, use the unsigned type (configurable)

If the column is self-incremented, the length must be greater than or equal to 4 bytes (configurable)

If it is of type timestamp, you must specify a default value.

For databases below the MySQL5.5 version (including), you cannot have two columns of type TIMESTAMP at the same time, and if it is a type of DATETIME, it cannot be defined as statements such as DATETIME DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

Default values need to be defined for each column, except for self-increment columns, primary key columns, and large field columns (configurable)

Cannot have duplicate column names

Index property check item

The index must have a name

Cannot have foreign keys (configurable)

Unique indexes must be prefixed with uniq_ (configurable)

Normal indexes must be prefixed with idx_ (configurable)

The number of columns in the index cannot exceed 5 (the number can be configured)

The table must have a primary key (configurable)

Up to 5 indexes (the number is configurable)

When indexing, the specified column must exist.

Columns in the index, which cannot be repeated

BLOB column cannot be built as KEY

Index length cannot exceed 766

There can be no duplicate indexes, names and contents

Default check item

Columns of type BLOB/TEXT, cannot have default values other than NULL

For versions below MySQL5.5 (inclusive), you cannot have the default value of the function NOW () for columns of type DATETIME.

If the default value is set to function, it can only be NOW ().

If the default value is NULL, but the column type is NOT NULL, or it is a primary key column, or it is defined as a self-incrementing column, an error is reported.

Default values cannot be set for self-incrementing columns.

2. Insert statement

Insert statement check item

Whether the table exists or not

You must specify the insert list, that is, for which columns you want to specify insert values, such as insert into t (id,id2) values (...), (configurable)

You must specify a list of values, and what the values are inserted in the corresponding columns above must be specified.

The number of inserted column lists is the same as the number of values lists, and the number of the above two needs to be the same, and if no column list is specified (because it is configurable), the values list should be the same length as the number of table columns.

Column that is not null. If the inserted value is null, an error is reported (configurable)

The column that inserts the specified column name must exist.

Inserts the specified column list. The same column cannot appear more than once.

Simple expressions inserted into the values list are checked, but what is not specified one by one is included

3. Update and delete statements

Update and delete statement check items

Whether the table exists or not

Must have where condition (configurable)

Delete statements cannot have limit conditions (configurable)

Cannot have order by statements (configurable)

If the number of affected rows is more than 10000, the alarm will be given (the number can be configured)

Do a simple check on the expression WHERE condition, including what is not specified one by one

Do a simple check on the values list expression of the update column and do not specify one by one

Do a simple check on the update column object, mainly to check whether the column exists, etc.

When multiple tables are updated or deleted, each table must exist

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.