A variable is cut in a wrong way but now sure why?

Question

I'm writing a script that locates all branches of a specific repo that haven't received any commits for more than 6 months and deletes them (after notifying committers). This script will run from Jenkins every week, will store all these branches in some MySQL database and then in the next run (after 1 week), will pull the relevant branch names from the database and will delete them.

I want to make sure that if for some reason the script is run twice on the same day, relevant branches will not get added again to the database, so I check it using a SQL query:

def insert_data(branch_name):
    try:
        connection = mysql.connector.connect(user=db_user,
                                             host=db_host,
                                             database=db_name,
                                             passwd=db_pass)
        cursor = connection.cursor(buffered=True)
        insert_query = """insert into {0}
        (
        branch_name
        )
        VALUES
        (
        \"{1}\"  
        ) where not exists (select 1 from {0} where branch_name = \"{1}\" and deletion_date is NULL) ;""".format(
            db_table,
            branch_name
        )
        cursor.execute(insert_query, multi=True)
        connection.commit()
    except Exception as ex:
        print(ex)
    finally:
        cursor.close()
        connection.close()

When I run the script, for some reason, the branch_name variable is cut in the middle and then the query that checks if the branch name already exists in the database fails:

1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where not exists (select 1 from branches_to_delete where branch_name = `AUT-1868' at line 8

So instead of checking for 'AUT-18681_designer_create_new_name_if_illegal_char_exist' it checks for 'AUT-1868' which doesn't exist in the database.

I've tried the following:

'{1}'
"{1}"
{1}

But to no avail.

What am I doing wrong?

I’ve tried without the back-slashes as well but I get the same error — Itai Ganot, Jul 26 '20 at 13:10
sql uses single quotes, but really, you need to learn how to use parameterized queries (they will make this a non-issue, and will also prevent the sql-injection attacks that are possible with the current code). — thebjorn, Jul 26 '20 at 13:12
An example of parameterized queries is here: https://stackoverflow.com/questions/775296/mysql-parameterized-queries For syntax errors it's also a great idea to work the query out in e.g. navicat (https://www.navicat.com/en/products/navicat-for-mysql) before calling it from python. — thebjorn, Jul 26 '20 at 13:22

Booboo · Answer 1 · 2020-07-26T16:55:27.127

~~I don't see anything wrong~~ assuming the source of the branch_name is safe (you are not open to SQL Injection attacks), but as an experiment you might try:

insert_query = f"""insert into {db_table}(branch_name) VALUES(%s) where not exists
    (select 1 from {db_table} where branch_name = %s and deletion_date is NULL)"""
cursor.execute(insert_query, (branch_name, branch_name))

I am using a prepared statement (which is also SQL Injection-attack safe) and thus passing the branch_name as a parameters to the execute method and have also removed the multi=True parameter.

Update

I feel like a bit of a dummy for missing what is clearly an illegal WHERE clause. Nevertheless, the rest of the answer suggesting the use of a prepared statement is advice worth following, so I will keep this posted.

Slava Rozhnev · Accepted Answer · 2020-07-26T14:17:53.623

Using WHERE statement in INSERT INTO query is illegal:

INSERT INTO `some_table`(`some_column`) 
VALUES ('some_value') WHERE [some_condition]

So, the above example is not valid MySQL query. For prevent duplication of branch_name you should add unique index on your table like:

ALTER TABLE `table` ADD UNIQUE INDEX `unique_branch_name` (`branch_name`);

And after this you can use next query:

INSERT INTO `table` (`branch_name`)  VALUES ('branch_name_1') 
ON DUPLICATE KEY UPDATE `branch_name` = `branch_name`;

Pay attention: If your table have auto-increment id, it will be incremented on each insert attempt

Since MySQL 8.0 you can use JASON_TABLE function for generate pseudo table from your values filter it from already exists values and use it fro insert. Look here for example

A variable is cut in a wrong way but now sure why?

2 Answers2