有没有安全的方法可以在MySQL查询中参数化数据库名称? [英] Is there any safe way to parameterize database names in MySQL queries?

问题描述

我正在编写一个小python脚本来帮助我自动为个人项目创建mysql数据库和相关帐户.该脚本的一部分是一个函数，该函数将数据库名称作为字符串，然后创建数据库.

I'm writing a little python script to help me automate the creation of mysql databases and associated accounts for my personal projects. Part of this script is a function that takes the database name as a string, then goes to create the database.

def createDB(dbConn, dbName):
    import MySQLdb
    c = dbConn.cursor()
    query = """CREATE DATABASE %s;""";
    c.execute(query, (dbName,))

这不起作用，因为 MySQL的CREATE DATABASE 要求数据库的未引用名称，如

This doesn't work because MySQL's CREATE DATABASE asks for the unquoted name of the database, as in

  CREATE DATAbASE test_db

但是我试图安全地将用户提供的数据库名称插入查询中的代码创建了

but my code that attempts to safely insert the user provided db name into the query creates:

  CREATE DATABASE 'test_db'

您会得到您的MySQL语法在测试附近存在问题".

And you get "you have a problem in your MySQL syntax near test".

即使这是供个人使用，我真的也不想直接将用户提供的字符串插入任何类型的查询中.它违反了我的宗教信仰.有没有安全的方法可以将用户提供的数据库名称插入python(或任何语言)的mySQL查询中，以确保诸如test_db; DROP some_other_db;之类的用户输入将被正确拒绝或转义?

Even though this is for personal use, I really don't want to just directly insert a user provided string into a query of any kind. Its against my religion. Is there a safe way to insert a user-provided database name into a mySQL query in python (or any language) that will make sure that user input such as test_db; DROP some_other_db; will get rejected or escaped correctly?

推荐答案

经过一番挖掘，事实证明phpmyadmin使用反引号来引用数据库，表和列名称.他们只是这样做:

After some digging it turns out that phpmyadmin uses backticks to quote database, table, and column names. They simply do:

$sql_query = 'CREATE DATABASE ' . PMA_backquote($new_db);  

在上面的错误情况下，会给出类似的信息

Which would give in the error case above something like

CREATE DATABASE `test_db; DROP some_other_db`;

当然，需要对输入字符串中的任何反引号进行转义，根据phpmyadmin的代码，这可以通过用双反勾号替换所有单反勾号来完成.我找不到任何地方可以确认这是正确的.

Of course any backticks in the input string need to be escaped, which according to phpmyadmin's code is done by replacing all single back ticks with double back ticks. I can't find any where that confirms that this is correct.

我也在线上注意到，尽管反引号不是标准的SQL.

I also noticed online though that backticks are not standard SQL.

这篇关于有没有安全的方法可以在MySQL查询中参数化数据库名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！