Breaking through MySQL view restrictions: Obtain SQL statements for creating views

The SQL statement used to create a view contains a load_file () function. To use this function, all the following conditions must be met:

The SQL statement used to create a view contains a load_file () function. To use this function, all the following conditions must be met:

In essence, a view is only an SQL statement, but MySQL does not store the SQL statement.
Instead, we save the view definition in the form of a file like a table, and use. frm to exist.
The SQL statement displayed with show create view is unfriendly.

The following describes a method to break through this restriction.

Create View:

Mysql> create view v_t as select id from t where id = 2;
Query OK, 0 rows affected (0.03 sec)

Find the view definition file in the corresponding directory:

[Mysql @ obe11g test] $ pwd
/Home/mysql/data/test
[Mysql @ obe11g test] $ ls-alh
Total 128 K
Drwxr-xr-x 2 mysql dba 4.0 K Jul 27 :45.
Drwxr-xr-x 5 mysql dba 4.0 K Jul 27 :19:13 ..
-Rw-r -- 1 mysql dba 65 Jun 19 10:20 db. opt
-Rw ---- 1 mysql dba 8.4 K Jul 24 19:58 t. frm
-Rw ---- 1 mysql dba 96 K Jul 27 :44 t. ibd
-Rwxrwxrwx 1 mysql dba 451 Jul 27 v_t.frm

First use show create view to query:

Mysql> show create view v_t;
+ ------ + --------------------- + ------------------------ + ---------------------- +
| View | Create View | character_set_client | collation_connection |
+ ------ + --------------------- + ------------------------ + ---------------------- +
| V_t | create algorithm = undefined definer = 'waterbin' @ 'localhost' SQL SECURITY DEFINER VIEW 'v _ t' AS select 'T '. 'id' AS 'id' from 't'where ('T '. 'id' = 2) | utf8 | utf8_general_ci |
+ ------ + --------------------- + ------------------------ + ---------------------- +
1 row in set (0.00 sec)

It contains a large number of escape characters, quotation marks, no code formatting, no comments, no indentation, etc. It is very readable and cannot be copied quickly to reconstruct the view.

Query the SQL statement used to create a view:

SELECT
REPLACE (
REPLACE (
SUBSTRING_INDEX (LOAD_FILE ('/home/mysql/data/test/v_t.frm '),
'\ Nsource =',-1 ),
'\ _', '\ _'), '\ %', '\ % '),'\\\\','\\'), '\ Z',' \ Z'), '\ t',' \ t '),
'\ R',' \ R'), '\ n',' \ n'), '\ B', '\ B '), '\\\"','\"'),'\\\'','\''),
'\ 0',' \ 0 ')
AS source;

The first line of the output result is the SQL statement:

+ Certificate ------------------------------------------------ +
| Source |
+ Certificate ------------------------------------------------ +
| Select id from t where id = 2
Client_cs_name = utf8
Connection_cl_name = utf8_general_ci
View_body_utf8 = select 'test '. 'T '. 'id' AS 'id' from 'test '. 't'where ('test '. 'T '. 'id' = 2)
|
+ Certificate ------------------------------------------------ +
1 row in set (0.00 sec)

The SQL statement used to create a view contains a load_file () function. To use this function, all the following conditions must be met:

① The file must be located on the server host

② You must specify the full path name to the file

③ You must have the FILE privilege

Verification: select user, file_priv from mysql. user;

④ The file must be readable by all

Reminder: all, not only OWNER and GROUP, but also OTHERE !!

⑤ Its size less than max_allowed_packet bytes

⑥ If the secure_file_priv system variable is set to a nonempty directory name

The file to be loaded must be located in that directory

Related reading:

Create and modify MySQL view charts

MySQL view)

,