Salt: MySQL grants - doesn't escaping correctly the special characters

[1ricardo]

Description of Issue/Question

Setup

State mysql_grants.present adding extra backslash to the database name, when we wants make the whitelisting of access.

Steps to Reproduce Issue

First we have to create simply user in DB

CREATE USER test@'%' IDENTIFIED BY 'some_password';

Now we can use this simple state:

percona_rights:
  mysql_grants.present:
    - grant: ALL
    - database: "web%.*"
    - user: test
    - host: "%"
    - revoke_first: True
````

SALT adding extra **backslash** to **%** in database name, what is incorrect

```sh
mysql> show grants for 'test'@'%';
+-------------------------------------------------+
| Grants for test@%                               |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%'                |
| GRANT ALL PRIVILEGES ON `web\%`.* TO 'test'@'%' |
+-------------------------------------------------+
2 rows in set (0.00 sec)

Should be:

mysql> show grants for 'test'@'%';
+-------------------------------------------------+
| Grants for test@%                               |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%'                |
| GRANT ALL PRIVILEGES ON `web%`.* TO 'test'@'%'  |
+-------------------------------------------------+
2 rows in set (0.00 sec)

Versions Report

Salt Version:
Salt: 2017.7.4
Dependency Versions:
cffi: 1.6.0
cherrypy: Not Installed
dateutil: 1.5
docker-py: Not Installed
gitdb: Not Installed
gitpython: Not Installed
ioflo: Not Installed
Jinja2: 2.7.2
libgit2: Not Installed
libnacl: Not Installed
M2Crypto: 0.21.1
Mako: Not Installed
msgpack-pure: Not Installed
msgpack-python: 0.5.6
mysql-python: Not Installed
pycparser: 2.14
pycrypto: 2.6.1
pycryptodome: Not Installed
pygit2: Not Installed
Python: 2.7.5 (default, Jul 13 2018, 13:06:57)
python-gnupg: Not Installed
PyYAML: 3.11
PyZMQ: 15.3.0
RAET: Not Installed
smmap: Not Installed
timelib: Not Installed
Tornado: 4.2.1
ZMQ: 4.1.4
System Versions:
dist: centos 7.5.1804 Core
locale: UTF-8
machine: x86_64
release: 3.10.0-693.21.1.el7.x86_64
system: Linux
version: CentOS Linux 7.5.1804 Core

[1ricardo]

When I disable the escape with this example:

percona_rights:
  mysql_grants.present:
    - grant: ALL
    - database: "web%.*"
    - user: test
    - host: "%"
    - revoke_first: True
    - escape: False

than I have following error

salt-call state.sls percona.test3
[INFO    ] Loading fresh modules for state activity
[INFO    ] Fetching file from saltenv 'base', ** done ** 'percona/test3.sls'
[INFO    ] Running state [percona_rights] at time 23:46:18.694012
[INFO    ] Executing state mysql_grants.present for [percona_rights]
[ERROR   ] An exception occurred in this state: Traceback (most recent call last):
File "/usr/lib/python2.7/site-packages/salt/state.py", line 1851, in call
**cdata['kwargs'])
File "/usr/lib/python2.7/site-packages/salt/loader.py", line 1795, in wrapper
return f(*args, **kwargs)
File "/usr/lib/python2.7/site-packages/salt/states/mysql_grants.py", line 191, in present
grant, database, user, host, grant_option, escape, ssl_option, **connection_args
File "/usr/lib/python2.7/site-packages/salt/modules/mysql.py", line 1857, in grant_add
_execute(cur, qry['qry'], qry['args'])
File "/usr/lib/python2.7/site-packages/salt/modules/mysql.py", line 548, in _execute
return cur.execute(qry, args)
File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 168, in execute
query = self.mogrify(query, args)
File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 147, in mogrify
query = query % self._escape_args(args, conn)
TypeError: * wants int
[INFO    ] Completed state [percona_rights] at time 23:46:18.720511 duration_in_ms=26.5
local:
----------
ID: percona_rights
Function: mysql_grants.present
Result: False
Comment: An exception occurred in this state: Traceback (most recent call last):
File "/usr/lib/python2.7/site-packages/salt/state.py", line 1851, in call
**cdata['kwargs'])
File "/usr/lib/python2.7/site-packages/salt/loader.py", line 1795, in wrapper
return f(*args, **kwargs)
File "/usr/lib/python2.7/site-packages/salt/states/mysql_grants.py", line 191, in present
grant, database, user, host, grant_option, escape, ssl_option, **connection_args
File "/usr/lib/python2.7/site-packages/salt/modules/mysql.py", line 1857, in grant_add
_execute(cur, qry['qry'], qry['args'])
File "/usr/lib/python2.7/site-packages/salt/modules/mysql.py", line 548, in _execute
return cur.execute(qry, args)
File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 168, in execute
query = self.mogrify(query, args)
File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 147, in mogrify
query = query % self._escape_args(args, conn)
TypeError: * wants int
Started: 23:46:18.694011
Duration: 26.5 ms
Changes:

but not sure if this is expected behavior

[Ch3LL]

possible duplciate of https://github.com/saltstack/salt/issues/48507 which was fixed in https://github.com/saltstack/salt/pull/48561 ?

[1ricardo]

Hi @Ch3LL,
I think this is something else, I created my own patch to fix that issue.
I am not sure if this is the best solution, but for now it's working.

Here is a diff for /usr/lib/python2.7/site-packages/salt/modules/mysql.py

--- /usr/lib/python2.7/site-packages/salt/modules/mysql.py 2018-08-24 10:01:17.959117184 +0200
+++ /usr/lib/python2.7/site-packages/salt/modules/mysql.py 2018-08-24 10:01:58.165903626 +0200
@@ -523,7 +523,7 @@
     ''' 
     if for_grants:
         return '`' + identifier.replace('`', '``').replace('_', r'\_') \
-            .replace('%', r'\%%') + '`'
+            .replace('%', r'%%') + '`'
     else:
         return '`' + identifier.replace('`', '``').replace('%', '%%') + '`' 

[Ch3LL]

want to push a PR with that?

[1ricardo]

Hi @Ch3LL,

Sure, I can do it

[ymasson]

Hi,

@Ch3LL , I have pushed a PR.