Cannot insert records that contain %

Question

I have inherited some Python code that uses SQLAlchemy to write to a Postgres database

The important parts of the code are:

from sqlalchemy import *

connection_string = "postgres://user,pasword,server&port/db"

Then there is a generic function that does this:

    db = create_engine(connection_string)
    cmd = "INSERT INTO TABLE_NAME (a_id,b_id,c) values (" + str(a_id) + ", " + str(b_is) + ", '" + (c_field_value) + "');"
    result = db.execute(cmd)

Probably not the best, but it works UNLESS c_field_value contains the % character

I've discovered this because the INSERT works perfectly with no % but it crashes when you do have a %

I've also added this before the function call

    c_field_value = re.sub('\%', ' PCT', c_field_value)

And the INSERT works fine, also if I insert the record manually (CLI) into Postgres it works.

So without a massive refactor, how do I get the existing code to insert the % properly please ?

Please read about SQL injection: https://en.m.wikipedia.org/wiki/SQL_injection. — Ilja Everilä, Oct 17 '20 at 07:06
And for how to safely pass arguments, check https://stackoverflow.com/questions/19314342/python-sqlalchemy-pass-parameters-in-connection-execute — Ilja Everilä, Oct 17 '20 at 07:13

score 0 · Answer 1 · answered Oct 18 '20 at 00:01

0

I added this line before the execute()

cmd = text(cmd)

and it works perfectly now. Thanks for the help :)

answered Oct 18 '20 at 00:01

cmcau

555
2
10

Cannot insert records that contain %

1 Answers1