如何避免MySQL / BaSH中的特殊/转义字符

I have been working to insert strings from text files into a MySQL database. However in these strings there could be random escape characters that will effect either BaSH or MySQL. Originally I was trying to pass the entire text file of characters to MySQL and deal with it in there. However now I think it may be better to cut up the data and deal with it mostly in BaSH.

我一直在努力将文本文件中的字符串插入MySQL数据库。但是在这些字符串中可能存在随机转义字符，这些字符会影响BaSH或MySQL。最初我试图将整个文本文件传递给MySQL并在那里处理它。但是现在我觉得削减数据并在BaSH中处理它可能会更好。

I am running everything from script files and am not the best with BaSH, I actually work mostly with MySQL or other languages like C# or Java.

我正在运行脚本文件中的所有东西，并不是最好的BaSH，我实际上主要使用MySQL或其他语言，如C＃或Java。

I know that you can embed other languages into BaSH scripts however in the end I will still need to pass strings to MySQL to insert and I need to preserve the original values of the text files.

我知道你可以将其他语言嵌入到BaSH脚本中，但最后我还是需要将字符串传递给MySQL才能插入，我需要保留文本文件的原始值。

So, is there a way to get these strings to MySQL with the languages to just ignore the characters in the string so it will preserve it and not cause issues?

那么，有没有办法让这些字符串使用语言来忽略字符串中的字符，以便它保留它而不会导致问题？

#!/bin/bash
cd /srv/mysql_PROJ/DB_scripts
echo ""
for dir in `cat ../pathlist.txt`; do
    for file in `/bin/ls -1 ${dir}/*.txt`; do
echo "Processing: dir(${dir}) / file(${file}); `cat ${file} | wc`"
    mysql -u rpm -p'password' --show-warnings --max_allowed_packet=16M call rpmdb.DataButcher( _utf8'`cat ${file}`');

    echo "Data inserted for:  `cat ${dir}`"
    echo ""
done
done
echo "Data parse and insert: Done."

This is the code I had before then I had a much more complex MySQL procedure that dealt with it. From what I could tell it would hit a character and pass only portions of the text to MySQL which then caused problems in MySQL, and the following lines in the text file tried to run as BaSH and obviously they weren't BaSH commands. Hopefully this will help, this is a older project that I keep coming back to and last I looked at it I got hitched up. With a new and fresh mind I am trying to approach it differently.

这是我之前的代码，然后我有一个更复杂的MySQL程序来处理它。从我可以告诉它它将击中一个字符并只将部分文本传递给MySQL，然后在MySQL中引起问题，并且文本文件中的以下行试图作为BaSH运行，显然它们不是BaSH命令。希望这会有所帮助，这是一个较旧的项目，我一直回到最后，我看着它，我已经结束了。有了新的头脑，我试图以不同的方式处理它。

NOTE: the procedure in MySQL is called "DataButcher"

注意：MySQL中的过程称为“DataButcher”

1 个解决方案

#1

1. Preferably don't use bash and the command line client instead use a scripting language like perl with mysql libraries. There you can use placeholders ? instead of string values and pass them arguments separately outside of the SQL.

   最好不要使用bash，而命令行客户端则使用像perl这样的脚本语言和mysql库。在那里你可以使用占位符？而不是字符串值，并在SQL之外单独传递它们的参数。

2. At a pinch you can use mysql UNHEX function

   在紧要关头，你可以使用mysql UNHEX函数

   INSERT INTO foo (bar) VALUES (UNHEX("536166652066726f6d2053514c20696e6a656374696f6e2027"));

   INSERT INTO foo（bar）VALUES（UNHEX（“536166652066726f6d2053514c20696e6a656374696f6e2027”））;