在使用here-document时对脚本变量分配进行bash

I am new to bash /mysql however I have found tons of help reading threw examples and other people's problems ...but have ran into one of my own . I am attempting to insert a row into an MySQL database table every time a file is added to a specific directory (for this i am using inotifywait ) anyways here is my bash script

我是bash /mysql新手，但是我已经找到了大量的帮助阅读抛出的例子和其他人的问题……但是我遇到了我自己的一个。我尝试在每次将一个文件添加到一个特定的目录(为此我正在使用inotifywait)时将一行插入到MySQL数据库表中，这里是我的bash脚本。

#!/bin/bash

while true; do
        filename= "false"
        filename= inotifywait --format "%f" -e create /var/www/media2net/torrent
        date_field= date +"%F":"%T"
        mysql --host=localhost --user=root --password=admin Media2net << EOF
        insert into video (title, description, url_video, upload_date)
                values('testing','default_description','$filename', '$date_feild');
        EOF
        echo $filename
done

From this I have verified with echo the variable $filename is properly held at end of bash script however when i look at entry in the table the column url_video has it's default value and not the string represented by $filename

我已经用echo验证过变量$filename在bash脚本末尾被正确保存，但是当我查看表中的条目时，url_video列具有默认值，而不是由$filename表示的字符串

From what i can conclude the variable $filename does not get passed through EOF i have tried as indicate here http://www.dba-oracle.com/t_access_mysql_with_bash_shell_script.htm

根据我的结论，变量$filename没有通过EOF传递，我在这里尝试了http://www.dba-oracle.com/t_access_mysql_with_bash_shell_script.htm

as well as this

以及这

Using shell script to insert data into remote MYSQL database

使用shell脚本将数据插入到远程MYSQL数据库中

any help of where i can find how to pass variable into query would be greatly appreciated

如果我能找到如何将变量传递到查询中的任何帮助，我将非常感激

1 个解决方案

#1

In your example, filename is set to the empty string (mind the spaces after the = sign!). You need

在您的示例中，文件名被设置为空字符串(注意=符号后面的空格!)你需要

filename=$(inotifywait --format "%f" -e create /var/www/media2net/torrent)

Similarly,

同样的,

date_field=$(date +"%F:%T")

and be careful, you have a typo in your mysql command (date_field and note date_feild):

注意，mysql命令中有一个错码(date_field和note date_fefields):

mysql --host=localhost --user=root --password=admin Media2net <<EOF
insert into video (title, description, url_video, upload_date)
values('testing','default_description','$filename', '$date_field');
EOF

Now I hope that you're controlling the filenames. Imagine a filename that contains a single quote e.g., hello'howdy. You'll have a problem in your query. Worse, an evil user who puts a file named whatever','something'); evil_mysql_command; whatever, you'll have the evil command performed! One possibility is to sanitize the filename using printf thus:

现在我希望您正在控制文件名。想象一个文件名包含一个引用，例如，hello'howdy。您的查询会有问题。更糟糕的是，一个恶意用户将一个文件命名为“某某”、“某某”);evil_mysql_command;无论如何，你将会执行邪恶的命令!一种可能的方法是使用printf来清除文件名:

printf -v filename '%q' "$(inotifywait --format "%f" -e create /var/www/media2net/torrent)"

This will at least escape the single quotes that could appear in a filename. See Gordon Davisson's comment: the printf trick will not prevent from all the possible attacks, so I really hope you absolutely control the name of the files!

这至少将转义文件名中可能出现的单引号。参见Gordon Davisson的评论:printf技巧不会阻止所有可能的攻击，所以我真的希望您绝对控制文件的名称!

All these suggestions yield the following script:

所有这些建议产生了以下脚本:

#!/bin/bash

while true; do
    printf -v filename '%q' "$(inotifywait --format "%f" -e create /var/www/media2net/torrent)"
    date_field=$(date +"%F:%T")
    mysql --host=localhost --user=root --password=admin Media2net <<EOF
    insert into video (title, description, url_video, upload_date) 
    values('testing','default_description','$filename', '$date_field');
    EOF
    echo "$filename"
done

Edit.

To answer your question in the comment:

在评论中回答你的问题:

why did the script properly echo $filename to my terminal but not send it properly to MySQL, does that have to do with string starting with a space? or something else completely?

为什么脚本正确地将$filename返回到我的终端，而没有正确地将它发送到MySQL，这与以空格开头的字符串有关系吗?完全或者别的什么?

That's because when you do something like:

这是因为当你做某事时

whatever= command

then the variable whatever is set to the empty string, and the command command is executed (with the whatever variable set as environment variable). E.g.,

然后将该变量设置为空字符串，并执行命令(将该变量设置为环境变量)。例如,

$ IFS='c' read a b c <<< "AcBcC"
$ echo "$a $b $c"
A B C
$ echo $IFS

$

In your script, the variable filename was in fact never globally set. You can check it by doing this:

在您的脚本中，变量文件名实际上从未被全局设置。

$ filename= "false"
$ echo "$filename"

$

What happens is that the environment variable filename is set to empty string then the command false (which happens to exist) is launched using that environment variable and we're done.

所发生的是，环境变量文件名被设置为空字符串，然后命令false(碰巧存在)使用该环境变量启动，我们就完成了。

When you do this:

当你这样做:

filename= inotifywait --format "%f" -e create /var/www/media2net/torrent

the variable filename is set to the empty string, and then the command inotifywait ... is executed with filename as an environment variable (but inotifywait doesn't really care about it). And that's what you saw on your terminal! it was the output of this command. Then you probably saw an empty line, that was the output of

变量文件名设置为空字符串，然后命令inotifywait…以文件名作为环境变量执行(但inotifywait并不真正关心它)。这就是你在终端上看到的!它是这个命令的输出。然后你可能会看到一条空线，这是输出

echo $filename

which was equivalent to

这是相当于

echo

since the variable filename expanded to an empty string.

因为变量文件名扩展为空字符串。

Hope this helps.

希望这个有帮助。