CODEWITHSUNDEEP
mysqllinuxbash
Saad Ismail
Saad Ismail

Reputation: 1440

Bash variable under a mysql query

I'm writing a bash script to clean wordpress based websites easily. I've a MySQL query to run with respect to a bash variable which I'm unable to figure out.

Since most of wordpress security plugins change database's table prefix so I want to use:

db_prefix=$(cat wp-config.php | grep "\$table_prefix" | cut -d \' -f 2)

to know of the exact table prefix which is in use & then use this MySQL query:

mysql -D${db_name} -u${db_user} -p${db_pass} << "EOF"
SELECT *
FROM  `wp_options`
WHERE  `option_name` LIKE  'template';
EOF

Now I want to use $db_prefix as variable on 3rd line of MySQL query by replacing wp_ with $db_prefix.

Is there any way to do so?

EDIT: I didn't know that it's that active, I really appreciate all of contributers, you'll see me contributing here too.

Bash script is live here now: https://github.com/saadismail/wp-clean

Thanks to all of you guys & girls...

Upvotes: 3

Views: 10338

Answers (2)

janos
janos

Reputation: 124646

First of all, the one-liner shell script that tries to extract db_prefix is incorrect. This way should work:

db_prefix=$(grep '^$table_prefix' wp-config.php | cut -d_ -f3)

After you run this command, verify it:

echo $db_prefix

Perhaps you didn't know how to embed the ${db_prefix} variable inside the here-document? Like this: wp_${db_prefix}_options.

But to be able to embed variables, then the starting EOF marker of the here-document cannot be enclosed in double-quotes, "EOF", because then variables will not be interpolated.

But then, if you change "EOF" to EOF, a remaining issue is the backticks, which would be executed by the shell as sub-shells, but you need to include them literally. One way to fix that is to omit them, as they are not really necessary.

Putting it together:

mysql -D${db_name} -u${db_user} -p${db_pass} << EOF
SELECT *
FROM  wp_${db_prefix}_options
WHERE  option_name LIKE  'template';
EOF

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

you must do as String

mysql -D${db_name} -u${db_user} -p${db_pass} -e "SELECT * FROM wp_${db_prefix}_options WHERE  option_name LIKE  'template';"

and it work.

or use backslash at the end of line

echo "SELECT * \
FROM  wp_${db_prefix}_options \
WHERE  option_name LIKE  'template " | mysql -D${db_name} -u${db_user} -p${db_pass}

Upvotes: 3

Related Questions