search-replace doesn't quote SQL values #58
Description
Original issue in wp-cli/wp-cli#4631 by @miraline:
The wp search-replace command with --regex and --all-tables-with-prefix generates invalid SQL queries.
Reproducing on a fresh install:
an@an:~/tmp/wp $ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 14.04.5 LTS
Release: 14.04
Codename: trusty
an@an:~ $ mkdir tmp/wp
an@an:~ $ cd tmp/wp
an@an:~/tmp/wp $ wp cli version
WP-CLI 1.4.1
an@an:~/tmp/wp $ wp core download
Downloading WordPress 4.9.2 (en_US)...
md5 hash verified: 6961904477ab2b7a53374326a07cb819
Success: WordPress downloaded.
an@an:~/tmp/wp $ mysql -u root -p -e 'create database wp_test'
Enter password:
an@an:~/tmp/wp $ wp config create --dbname=wp_test --dbuser=root --dbpass=''
Success: Generated 'wp-config.php' file.
an@an:~/tmp/wp $ wp core install --skip-email --url=wptest.local --title=test --admin_user=admin --admin_password=123456 --admin_email=root@wptest.local
Success: WordPress installed successfully.
an@an:~/tmp/wp $ cat /tmp/tbl.sql
DROP TABLE IF EXISTS `wp_123_test`;
CREATE TABLE `wp_123_test` (
`name` varchar(50),
`value` varchar(5000),
`created_at` datetime NOT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`name`)
) ENGINE=InnoDB;
INSERT INTO `wp_123_test` VALUES ('test_val','off','2016-11-15 14:41:33','2016-11-15 21:41:33');
an@an:~/tmp/wp $ mysql -u root wp_test < /tmp/tbl.sql
an@an:~/tmp/wp $ wp search-replace --dry-run --regex 'mytestdomain.com\/' 'mytestdomain2.com/' --skip-themes --skip-plugins --all-tables-with-prefix --skip-columns=guid,domain 2>&1 | head -c 1000
WordPress database error Unknown column 'test_val' in 'where clause' for query SELECT `name` FROM `wp_123_test` WHERE `name` = test_val made by include('phar:///usr/local/bin/wp/php/boot-phar.php'), include('phar:///usr/local/bin/wp/php/wp-cli.php'), WP_CLI\bootstrap, WP_CLI\Bootstrap\LaunchRunner->process, WP_CLI\Runner->start, WP_CLI\Runner->_run_command_and_exit, WP_CLI\Runner->run_command, WP_CLI\Dispatcher\Subcommand->invoke, call_user_func, WP_CLI\Dispatcher\CommandFactory::WP_CLI\Dispatcher\{closure}, call_user_func, Search_Replace_Command->__invoke, Search_Replace_Command->php_handle_col
WordPress database error Unknown column 'test_val' in 'where clause' for query SELECT `value` FROM `wp_123_test` WHERE `name` = test_val made by include('phar:///usr/local/bin/wp/php/boot-phar.php'), include('phar:///usr/local/bin/wp/php/wp-cli.php'), WP_CLI\bootstrap, WP_CLI\Bootstrap\LaunchRunner->process, WP_CLI\Runner->start, WP_CLI\Runner->_run_command_and_exit, WP_CLI\Runner->run_command,an@an:~/tmp/wp $ ^C
It looks like test_val is not quoted and thus is treated like a table name, not a value.
In the same time, it works just fine without --regex and/or --all-tables-with-prefix:
an@an:~/tmp/wp $ wp search-replace --dry-run 'mytestdomain.com\/' 'mytestdomain2.com/' --skip-themes --skip-plugins --all-tables-with-prefix --skip-columns=guid,domain 2>&1 | head -c 1000
Table Column Replacements Type
wp_123_test name 0 SQL
wp_123_test value 0 SQL
wp_commentmeta meta_key 0 SQL
wp_commentmeta meta_value 0 SQL
wp_comments comment_author 0 SQL
wp_comments comment_author_email 0 SQL
wp_comments comment_author_url 0 SQL
wp_comments comment_author_IP 0 SQL
wp_comments comment_content 0 SQL
wp_comments comment_approved 0 SQL
...