通过Bash脚本语言逃避MYSQL命令行。

PHP has mysql_real_escape_string() to correctly escape any characters that might cause problems. What is the best way to mimic this functionality for BASH?

PHP具有mysql_real_escape_string()以正确转义任何可能导致问题的字符。模拟BASH的这种功能的最佳方法是什么?

Is there anyway to do prepared mysql statements using bash? This seems to be the best way.

使用bash是否有准备好的mysql语句?这似乎是最好的办法。

Most of my variables won't (shouldn't) have special characters, however I give the user complete freedom for their password. It may include characters like ' and ".

我的大多数变量不会(不应该)有特殊的字符，但是我给了用户完全*的密码。它可能包括“和”这样的角色。

I may be doing multiple SQL statements so I'll want to make a script that takes in parameters and then runs the statement. This is what I have so far:

我可能正在做多个SQL语句，所以我想要创建一个脚本，它接收参数，然后运行语句。这是我目前所拥有的:

doSQL.sh:

#!/bin/sh

SQLUSER="root"
SQLPASS="passwor339c"
SQLHOST="localhost"

SQL="$1"
SQLDB="$2"


if [ -z "$SQL" ]; then echo "ERROR: SQL not defined"; exit 1; fi
if [ -z "$SQLDB" ]; then SQLDB="records"; fi

echo "$SQL" | mysql -u$SQLUSER -p$SQLPASS -h$SQLHOST $SQLDB

and an example using said command:

还有一个使用上述命令的例子:

example.sh:

PASSWORD=$1
doSQL "INSERT INTO active_records (password) VALUES ('$PASSWORD')"

Obviously this would fail if the password password contained a single quote in it.

显然，如果密码中包含一个引号，这将失败。

5 个解决方案

#1

In Bash, printf can do the escaping for you:

在Bash中，printf可以为您做转义:

$ a=''\''"\;:#[]{}()|&^$@!?, .<>abc123'
$ printf -v var "%q" "$a"
$ echo "$var"
\'\"\\\;:#\[\]\{\}\(\)\|\&\^\$@\!\?\,\ .\<\>abc123

I'll leave it to you to decide if that's aggressive enough.

我让你来决定这是否足够激进。

#2

This seems like a classic case of using the wrong tool for the job.

这似乎是一个典型的使用错误工具的例子。

You've got a lot of work ahead of you to implement the escaping done by mysql_real_escape_string() in bash. Note that mysql_real_escape_string() actually delegates the escaping to the MySQL library which takes into account the connection and database character sets. It's called "real" because its predecessor mysql_escape_string() did not take the character set into consideration, and could be tricked into injecting SQL.

要实现在bash中由mysql_real_escape_string()完成的转义，前面还有很多工作要做。请注意，mysql_real_escape_string()实际上将转义委托给了MySQL库，它考虑到连接和数据库字符集。它之所以被称为“real”，是因为它的前身mysql_escape_string()没有考虑字符集，可能会被骗去注入SQL。

I'd suggest using a scripting language that has a MySQL library, such as Ruby, Python, or PHP.

我建议使用具有MySQL库的脚本语言，如Ruby、Python或PHP。

If you insist on bash, then use the MySQL Prepared Statements syntax.

如果您坚持bash，那么使用MySQL准备好的语句语法。

#3

mysql_real_escape_string() of course only escapes a single string literal to be quoted, not a whole statement. You need to be clear what purpose the string will be used for in the statement. According to the MySQL manual section on string literals, for inserting into a string field you only need to escape single and double quotation marks, backslashes and NULs. However, a bash string cannot contain a NUL, so the following should suffice:

当然，mysql_real_escape_string()只转义要引用的单个字符串文字，而不是整个语句。您需要弄清楚字符串在语句中使用的目的是什么。根据字符串文本的MySQL手册部分，为了插入到字符串字段中，您只需要摆脱单引号、双引号、反斜杠和null字符。但是，bash字符串不能包含NUL，因此以下内容应该足够:

#escape for MySQL single string
PASSWORD=${PASSWORD//\\/\\\\}
PASSWORD=${PASSWORD//\'/\\\'}
PASSWORD=${PASSWORD//\"/\\\"}

If you will be using the string after a LIKE, you will also probably want to escape % and _.

如果您将在LIKE后面使用字符串，您可能还想转义%和_。

Prepared statements are another possibility. And make sure you don't use echo -e in your bash.

准备好的陈述是另一种可能性。确保在bash中不使用echo -e。

参见https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

#4

This will escape apostrophes

这将摆脱撇号

a=$(echo "$1" | sed s/"'"/"\\\'"/g)

Please note though that mysql_real_escape_string also escapes \x00, \n, \r, \, " and \x1a. Be sure to escape these for full security.

请注意，mysql_real_escape_string也可以转义\x00、\n、\r、\”和\x1a。为了安全起见，一定要避开这些。

To escape \x00 for example:

要转义\x00，例如:

a=$(echo "$1" | sed s/"\x00"/"\\\'"/g)

With a bit of effort you can probably escape these using one sed command.

通过一点努力，您可能可以使用一个sed命令来避免这些问题。

#5

-1

This will work:

这将工作:

echo "John O'hara"  | php -R 'echo addslashes($argn);'

To pass it to a variable:

将它传递给一个变量:

name=$(echo "John O'hara"  | php -R 'echo addslashes($argn);')

#1