Wildcards in a String?

[pajd]

Hello,

So Im trying to use python to locate a user in SQL and remove it. There will be more than one user that has a username beginning with ROadmin

For example ROadmin1, ROadmin2 and ROadmin3
Currently I can fetch all users and then us this to search for anything beginning with RO

if re.search('ROadmin.+', user):
                print(f" Read Only user {user} found.")
                

Now to remove the users I need to use the DROP statement thats used in SQL.
To prevent SQL injection (hacking) the placeholder %s to escape values in the delete statement is used like this

sql_GetUser = "DROP USER %s;"%userName;]

I then have userName as a list.
My question is how can my list contain a string that caters for something (like a wildcard) that can point to any used named ROadmin ?
I cant have a list containing the full name to DROP as to DROP a user I need to use a SQL statement of DROP USER 'ROadmin1'@'localhost'
As you can see this contains a list of letters, numbers and a sysmbol.

I need the list, if possible to contain something like a wildcard of 'ROadmin.*'
Is that possible? I tried that but it didnt work

Thanks

[deanhystad]

Sounds like you want to use LIKE.

https://www.sqlitetutorial.net/sqlite-like/

[pajd]

I did look online and couldnt see a working way to add Like% into a DROP statement.

My issue is how to search the results of the re.search and save them in a way that I can search using 'ROadmin' and get the results so the DROP statement can then be used to remove the results

[deanhystad]

LIKE is the only thing in SQL that takes a pattern. I thought you were trying to do something like delete rows in a table that have a matching pattern in some user column. I'm so used to only thinking about client requests that I automatically converted drop to delete. I should read carefully.

There is no LIKE for DROP. You'll have to retrieve the user list and loop through the names. I a name matches the pattern, drop the name.

[pajd]

So better to use this?
For loop
The statement I want to execute
Cursor execute
Else

The only issue with that is the DROP statement requires 'username'@'hostname' while the list only contains the 'username'

[deanhystad]

Do you know the hostname? If so it is easy to combine the username and hostname. Why not start out by getting the usernames and printing them out as 'username'@'hostname'.

[pajd]

Yes I know the hostname. It's the same for all usernames

So I print (users) and join it somehow to a string which is the hostname?

So far I have this but just unsure where to go from here?

sql_GetUser = "select user, host from mysql.user;"
    cursor.execute(sql_GetUser)
    logger.info("Got a list of users")
    users= cursor.fetchall ()
    print(users)
    for item in users:
        for user in item:
            if re.search('ROadmin.+', user):   

[rob101]

@pajd

As I'm sure deanhystad will point out, you need to be mindful of SQL injection attacks when you use string objects as you're doing here.

See: How to use placeholders to bind values in SQL queries

[pajd]

@pajd

As I'm sure deanhystad will point out, you need to be mindful of SQL injection attacks when you use string objects as you're doing here.

See: How to use placeholders to bind values in SQL queries

Yes Im mindful but thats causing me an issue when running my Drop query of

 sql_DeleteUser = "DROP USER %s;"%userName

I will have a few different users beginning with RO admin but the query needs the 'username@hostname' and I cant figure out how to run that if Im also trying to prevent inject attacks using that DROP query

[rob101]

I can see that you're using a debug print function print(users) (which is what I do as well) so you can see the list object that's being returned by users = cursor.fetchall(), but I'm unsure what is in said list.

To add: sorry, my bad -- I should have read the full thread. I'll do that now.