Wildcards in a String?

[pajd]

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.

It prints out the list of users that begin with Roadmin. These are the users I want to delete
However, after that Im not sure where to go as the DROP command I need to use (to prevent injection) cant specify the username and host name

[rob101]

Does this help?

admin_list = []
for index, user in enumerate(users,1):
        admin_list.append(user[0][:-1]+str(index))

print(admin_list)

Output:
['ROadmin1', 'ROadmin2', 'ROadmin3']

To add: from what I can see, we can get the host name from the db. I've got to go out for an hour or so, but I'll be back.

If you need just users that begin with 'ROadmin', then simply have a condition:

if user[0][:-1] == 'ROadmin':
    admin_list.append(user[0][:-1]+str(index))

[pajd]

Does this help?

admin_list = []
for index, user in enumerate(users,1):
        admin_list.append(user[0][:-1]+str(index))

print(admin_list)

Output:
['ROadmin1', 'ROadmin2', 'ROadmin3']

To add: from what I can see, we can get the host name from the db. I've got to go out for an hour or so, but I'll be back.

Your user output is based on ROadmin being 1, 2,3 etc
Our admins might created users that do have ROadmin at the beginning of the username but they might add something else onto the end for example ROadmindev or ROadminsupport

Getting the users is OK but be mindful that the DROP command cant contain 'username@hostname' (as we stated due to injection attacks)

So how can the DROP delete whats in your list if they dont contain @'hostname' ?

Thanks

[rob101]

Our admins might created users that do have ROadmin at the beginning of the username but they might add something else onto the end for example ROadmindev or ROadminsupport

See my updated post, with the if condition.

Ah... I'm late; got to go!

I'll be back in a while.

[pajd]

I can find the users using this

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('ROacc.+', user):
                cursor.execute(sql_GetUser)
                print(user) 

Output:
ROaccsupportone
ROaccsupportthree
ROaccsupporttwo  

Now I need to delete them with the DROP.
However the DROP needs to prevent injection attacks so needs to use this statement DROP USER %s;"%

Im not sure how this can be achieved? I need the output from print(user) to be added to maybe a list? And somehow the DROP statement picks up that list and deletes the users contained in it?

[rob101]

However the DROP needs to prevent injection attacks

That's why I had the code append to a list object. You can then index into said list and use placeholders in the sql command.

To add..

As an example, from my own code:

            if tables:
                table = ((tables[0])[0]) # get the name of the first (or only) table
                data = cur.execute(f'SELECT `_rowid_`,* FROM {table} ORDER BY `_rowid_` ASC LIMIT 0, 50000')
                items = data.fetchall()

... where tables is a list object.

[pajd]

I have appended into a list however the list only has the username for n there and not the hostname.
DROP needs both.

I do however know the name of the hostname name.
Is there a way to connect the list with the hostname?

[rob101]

Off of the top of my head; have a list of host names that is indexed in the same way as user names, so that you can use username[0] with hostname[0] etc.

[deanhystad]

You don't need to worry about sql injection when the database is providing the strings. Use an f"string to combine the user and host name, and to make the drop command string.

matches= []
for username in users:
    if re.match(pattern, username):
        matches.apend(f"'{username}'@'hostname'")
if matches:
    drop_cmd = "DROP USER " + ", ".join(matches)

[rob101]

I can find the users using this

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('ROacc.+', user):
                cursor.execute(sql_GetUser)
                print(user) 

Output:
ROaccsupportone
ROaccsupportthree
ROaccsupporttwo  

Now I need to delete them with the DROP.
However the DROP needs to prevent injection attacks so needs to use this statement DROP USER %s;"%

Im not sure how this can be achieved? I need the output from print(user) to be added to maybe a list? And somehow the DROP statement picks up that list and deletes the users contained in it?

Not too sure where you are with this or if you've implemented the code that deanhystad has posted.

About the code (above): it seems to me that the output is coming from line 5, the if at line 8 is never going to be True, even if it reads (as I think it should) if re.match('ROacc.+', user): because user (on the first iteration) will be ROaccsupportone so you're trying to match ROacc.ROaccsupportone, I think, but it's getting a little confusing at my end, as I don't have your database.

If you're still stuck, then post back. Maybe you need to ask either me or deanhystad, because I think that you could be getting confused, as we're both trying to help you in our own way, with different solutions.