通过 Bash 脚本转义 MYSQL 命令行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4383135/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Escaping MYSQL command lines via Bash Scripting
提问by ParoX
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:
doSQL.sh:
#!/bin/sh
SQLUSER="root"
SQLPASS="passwor339c"
SQLHOST="localhost"
SQL=""
SQLDB=""
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:
例子.sh：
PASSWORD=
doSQL "INSERT INTO active_records (password) VALUES ('$PASSWORD')"
Obviously this would fail if the password password contained a single quote in it.
显然，如果密码密码中包含单引号，这将失败。
采纳答案by Paused until further notice.
In Bash, printfcan 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.
我会让你来决定这是否足够激进。
回答by hobodave
This seems like a classic case of using the wrong tool for the job.
这似乎是在工作中使用错误工具的经典案例。
You've got a lotof 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 库，该库考虑了连接和数据库字符集。它被称为“真实”是因为它的前身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 Statementssyntax.
如果您坚持使用 bash，那么请使用MySQL Prepared Statements语法。
回答by Cedric Knight
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 手册部分，要插入字符串字段，您只需转义单引号和双引号、反斜杠和 NUL。但是，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 _.
如果您将在 a 之后使用字符串LIKE，您可能还想转义%和_。
Prepared statements are another possibility. And make sure you don't use echo -ein your bash.
准备好的语句是另一种可能性。并确保您不在echo -ebash 中使用。
See also https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
另见https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
回答by bcoughlan
This will escape apostrophes
这将逃避撇号
a=$(echo "" | 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 "" | sed s/"\x00"/"\\'"/g)
With a bit of effort you can probably escape these using one sed command.
通过一些努力，您可能可以使用一个 sed 命令来逃避这些。
回答by Garet Claborn
Sure, why not just use the real thing?
当然，为什么不直接使用真实的东西呢？
A script, anywhere, such as
~/scripts/mysqli_real_escape.php
任何地方的脚本，例如
~/scripts/mysqli_real_escape.php
#!/bin/php
<?php
$std_input_data = '';
$mysqli = new mysqli('localhost', 'username', 'pass', 'database_name');
if( ftell(STDIN) !== false ) $std_input_data = stream_get_contents(STDIN);
if( empty($std_input_data) ) exit('No input piped in');
if( mysqli_connect_errno( ) ) exit('Could not connect to database');
fwrite ( STDOUT,
$mysqli->real_escape_string($std_input_data)
);
exit(0);
?>
Next, run from bash terminal:
接下来，从 bash 终端运行：
chmod +x ~/script/mysqli_real_escape.php`
ln -s ~/script/mysqli_real_escape.php /usr/bin/mysqli_real_escape
All set! Now you can use
mysqli_real_escapein your bash scripts!
搞定！现在你可以
mysqli_real_escape在你的 bash 脚本中使用了！
#!/bin/bash
MyString="stringW@#)*special characters"
MyString="$(printf "$MyString" | mysqli_real_escape )"
Note: From what I understand, command substitution using "$(cmd ..."$var")"is preferred over using backticks. However, as no further nesting would be needed either should be fine.
注意：据我所知，使用命令替换"$(cmd ..."$var")"优于使用反引号。但是，因为不需要进一步的嵌套，所以应该没问题。
Further Note: When inside command substitution, "$(...)", a new quote context is created. This is why the quotes around variables do not screw up the string.
进一步注意：在内部命令替换时，"$(...)"会创建一个新的引用上下文。这就是为什么变量周围的引号不会弄乱字符串的原因。
回答by Tom
This is how I did it, where my-file.txtcontains spaces, new lines and quotes:
我就是这样做的，其中my-file.txt包含空格、新行和引号：
IFS='' content=$(cat my-file.txt)
mysql <flags> -e "update table set column = $(echo ${content@Q} | cut -c 2-) where something = 123"
回答by Shouguang Cao
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);')
