如何防止MySQL命令行shell界面中的SQL注入?

发布于 2024-10-08 05:31:10 字数 345 浏览 7 评论 0原文

我使用 shell 脚本与 MySQL 数据库通信。 MySQL 支持将查询指定为 shell 参数,如下所示:

mysql my_db -B -N -e "select id from Table"

但是,如果我有一个参数,我想在查询中使用该参数,那么如何获得针对注入攻击的保护?

一种简单的方法是将变量值粘贴到请求中,但这不是很安全:

mysql my_db -B -N -e "select id from Table where name='$PARAM'"

是否有任何技巧或记录的接口可以从命令行进行注入安全查询?

I use shell script to communicate to a MySQL database. MySQL supports specifying query as a shell argument, like this:

mysql my_db -B -N -e "select id from Table"

However, if I have a parameter, which I'd like to use in a query, how can I get protection against injection attacks?

A naive way is to just paste variable value to the request, but it's not very secure:

mysql my_db -B -N -e "select id from Table where name='$PARAM'"

Are there any tricks or documented interfaces to make an injection-safe queries from command line?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

万人眼中万个我 2024-10-15 05:31:10

您可以对值进行 Base64 编码,然后在 MySQL 中对其进行 Base64 解码。 MySQL中有UDF用于将Base64数据转换为普通数据。此外,大多数系统要么具有 uuencode,要么具有用于 Base64 编码数据的“base64”命令。

You can base64 encode the value, and then base64 decode it once it's in MySQL. There are UDFs in MySQL for converting Base64 data to common data. Additionally, most systems either have uuencode, or the 'base64' command for base64 encoding data.

鹤舞 2024-10-15 05:31:10

您不仅需要防止 SQL 注入,还需要防止 shell 注入。您可能希望将查询(在清理任何动态部分之后)写入文件,然后将该文件重定向到 mysql,而不是希望查询不会破坏 shell。考虑:

PARAM="name'\"; rm -rf / ; echo 'pwn3d U"

成为

mysql my_db -B -N -e "select id from Table where name='name'"; rm -rf / ; echo 'pwn3d U'

或:

command 1: mysql my_db -B -N -e "select id from Table where name='name'"
command 2: rm -rf /
command 3: echo 'pwn3d U'

相反,执行类似的操作:

 cat <<EOT > query.sql
 select .... blah blaah blah .... sanitized query here
 EOT
 mysql my_db -B -N < query.sql

这将防止任何用户指定的数据出现在 shell 命令本身中,从而防止至少一级的注入漏洞。但是,你仍然需要处理 SQL 注入问题。

You not only have to protect against SQL injection, but against shell injection as well. You may want to write the query (after sanitizing any dynamic parts) out to a file and then redirect that file into mysql, rather than hoping the query won't break the shell. Consider:

PARAM="name'\"; rm -rf / ; echo 'pwn3d U"

becoming

mysql my_db -B -N -e "select id from Table where name='name'"; rm -rf / ; echo 'pwn3d U'

or:

command 1: mysql my_db -B -N -e "select id from Table where name='name'"
command 2: rm -rf /
command 3: echo 'pwn3d U'

Instead, do something like:

 cat <<EOT > query.sql
 select .... blah blaah blah .... sanitized query here
 EOT
 mysql my_db -B -N < query.sql

This would prevent any user-specified data from ever appearing within the shell command itself, prevent at least one level of injection vulnerability. But then, you still have to handle the SQL injection problem.

栩栩如生 2024-10-15 05:31:10

每当您通过连接参数构造 SQL 时(如您的示例中所示),您的应用程序都容易受到 SQL 注入攻击。维基百科上有一篇关于此问题的文章,链接如下: http://en.wikipedia.org/wiki/SQL_injection

我怀疑您会想要编写一个 unix 过滤器来使用本文中提到的 mysql_real_escape_string 函数构建 SQL 查询。

考虑将 SQL 作为第一个参数传递,将变量作为后续参数传递,然后让它返回构造的 SQL。如果您将过滤器命名为“blobbo”,则示例中的命令可能如下所示:

blobbo "select id from Table where name=%s" $PARAM

Your application is succeptible to an SQL Injection attack any time you are construct SQL by concatenating parameters (as in your example). There is a writeup about this on wikipedia at this link: http://en.wikipedia.org/wiki/SQL_injection

I suspect that you will want to write a unix filter to construct your SQL query using the mysql_real_escape_string function mentioned in the article.

Consider passing the SQL as the first parameter and the variable(s) as the subsequent parameters then have it return the constructed SQL. If you name your filter "blobbo" the command like for your example might look like this:

blobbo "select id from Table where name=%s" $PARAM

舟遥客 2024-10-15 05:31:10

Sargun Dhillon 的回答为我指明了正确的方向。不幸的是,FROM_BASE64 在 MySQL 5.6 之前不可用,所以我选择了 UNHEX。

下面的脚本是一个从 shell 查询 Redmine 用户详细信息的示例。如果不受信任的用户可以访问此脚本,我仍然睡不好,但对我来说它足够安全。 (它也仅限于字符串值,并且您的查询中不应该有问号,但这些限制对我来说没有问题。)

#!/bin/bash

MYSQL_OPTS='--defaults-file=/etc/redmine/mysql.cnf'

mysql_query() {
  local db=$1
  local sql=$2
  shift 2 || return 1
  declare -a args=("$@")

  sql=${sql//[%]/%%}
  sql=${sql//[?]/UNHEX('%s')}
  for ((i=0; i<${#args[@]}; i++)); do
    args[$i]=$(echo -n "${args[$i]}" | hexdump -v -e '/1 "%02X"')
  done
  sql=$(printf "$sql" "${args[@]}")
  mysql $MYSQL_OPTS "$db" -e "$sql" || return $?
}

for u in "$@"; do
  mysql_query redmine 'select * from users where login=?\G' "$u" 
done

如果您发现我错过的任何 SQL 或 Shell 注入,请发表评论。

The answer by Sargun Dhillon pointed me to the right direction. Unfortunately is FROM_BASE64 not available before MySQL 5.6 so I went with UNHEX.

The script below is an example to query the details of a Redmine user from a shell. I still wouldn't sleep well if untrusted users had access to this script but it is safe enough in my case. (It is also limited to string values and you shouldn't have a question mark in your query but those limitations are fine with me.)

#!/bin/bash

MYSQL_OPTS='--defaults-file=/etc/redmine/mysql.cnf'

mysql_query() {
  local db=$1
  local sql=$2
  shift 2 || return 1
  declare -a args=("$@")

  sql=${sql//[%]/%%}
  sql=${sql//[?]/UNHEX('%s')}
  for ((i=0; i<${#args[@]}; i++)); do
    args[$i]=$(echo -n "${args[$i]}" | hexdump -v -e '/1 "%02X"')
  done
  sql=$(printf "$sql" "${args[@]}")
  mysql $MYSQL_OPTS "$db" -e "$sql" || return $?
}

for u in "$@"; do
  mysql_query redmine 'select * from users where login=?\G' "$u" 
done

If you discover any SQL or Shell injection I missed, please comment.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文