Bash variable under a mysql query

Question

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...

Bernd Buffen · Accepted Answer · 2015-10-11 17:43:10Z

3

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}

answered Oct 11, 2015 at 17:43

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

janos · Accepted Answer · 2015-10-11 17:51:48Z

1

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

edited Oct 11, 2015 at 17:51

answered Oct 11, 2015 at 17:30

janos

126k31 gold badges242 silver badges253 bronze badges

2 Comments

Saad Ismail Over a year ago

Actually I don't have any knowledge of mysql queries, I just copied that query from phpmyadmin. Tried updated script but this time it's just ignoring that variable & giving this error: ERROR 1146 (42S02) at line 1: Table 'wp30616db_16958._options' doesn't exist wp30616db_16958 is database name here.

janos Over a year ago

I see. Actually the way you extract the value of db_prefix from wp-config.php is broken too. I updated my post with the fix, see at the top.

Collectives™ on Stack Overflow

Bash variable under a mysql query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related