Python Forum

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

Sounds like you want to use LIKE.

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

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

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.

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'

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

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):   

@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

(Oct-11-2022, 08:45 AM)rob101 Wrote: [ -> ]@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

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.