MySQL Connector/Python 8.0.15: allow_local_infile Disabled by Default
source link: https://www.tuicool.com/articles/hit/AvU3Mvn
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
MySQL Server has a feature where you can insert data into a table from a file with the data delimited by commas, tabs, or another delimiter. This is particularly useful when you need to bulk import data, for example when restoring a backup or migrating data from one system to another including from another database product than MySQL. The mysqldump backup utility is an example of a program that supports exporting the data to delimited text files.
The statement to load the data is LOAD DATA INFILE
. By default the file must be server-side and MySQL Server will load it without involving the connections (other than for submitting the query and returning the result). However, there is also an optional keyword LOCAL
that can be used to tell MySQL that a the file is located client-side: LOAD DATA LOCAL INFILE
. It is this local variant that is the topic of this blog.
By default, MySQL Server 8 does not allow loading client-side files using the LOAD DATA LOCAL INFILE
statement. (Similarly the mysql
command-line client has the feature disabled by default.) Why? The reason is that there are security implications enabling the feature. For that reason, it is recommended to disable the feature unless you know you need it, and then only allow it for as short time as possible.
Advice
The server-side variant is safer as long as the server-side file system is secured. Additionally, MySQL will only read server-side files from the directory (including subdirectories) specified with the secure_file_priv option. Make sure the option is not set to an empty value as that will allow MySQL to read any file.
In MySQL Connector/Python, the mysql.connector
module includes the option allow_local_infile
which determines whether MySQL Connector/Python will allow the server to request a client-side file. In version 8.0.15 the default value for allow_local_infile
has been changed from True
to False
to align with the default value for MySQL Server and the mysql
command-line client.
This means that if you attempt to execute LOAD DATA LOCAL INFILE
in MySQL Connector/Python 8.0.15 without explicitly enabling it, a ProgrammingError
exception is raised:
mysql.connector.errors.ProgrammingError: 1148 (42000): The used command is not allowed with this MySQL version
(Admitted, the error message is not particularly good at telling what you are doing wrong.) What do you do, if you need to load client-side data files? These are the steps:
- Enable the
local_infile
option in MySQL Server. This only exists for the global scope. You can temporary enable it withSET GLOBAL local_infile = ON;
, then disable it again when you are done by setting the variable toOFF
. - Enable
allow_local_infile
in your MySQL Connector/Python program. I will show an example of how to do this below. Again, it is recommended to only have it enabled when needed.
An example of creating a connection with allow_local_infile
enabled is:
import mysql.connector # Create connection to MySQL db = mysql.connector.connect( option_files="my.ini", allow_local_infile=True )
This example uses an INI formatted configuration file to specify the connection options, and the allow_local_infile
option is added explicitly.
If you are interested in learning more about using LOAD DATA [LOCAL] INFILE
with MySQL Connector/Python, then my book MySQL Connector/Python Revealed (Apress) has a section describing how to load data from files including an example. The book is available from Apress (print and DRM free ePub+PDF), Amazon (print and Kindle), Barnes & Noble (print), and others.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK