Database.execute duplicating percentage sign

[lukashambsch]

I'm using a raw sql insert statement, but the percentage sign is being duplicated. I've tracked it down to this line in the code.

pony/pony/orm/core.py

Line 224 in 08bce0b

if paramstyle in ('format', 'pyformat'): sql = sql.replace('%', '%%')

Is there a way to get around this? Possibly somewhere I should be setting the paramstyle?

[kozlovsky]

What database are you using? Can you show an example of query?

[lukashambsch]

--

[kozlovsky]

Thanks for reporting, I was able to reproduce the bug.

The problem happens when the query (1) contains % sign inside a literal value and (2) does not have any $parameter.

We will fix it in next release. But it is not a good practice to put values inside SQL query text, because it can lead to SQL injection. I recommend you to rewrite a query using query parameters:

vals = [
    ('Marshalls_20180109_4_22.00/10.5%', 'B01FI911QO', '', 2, 1),
    ('4B-DAJQ-OM2O', 'B00JBF45W4', 'Capri Designs- Daily Journal - Hedgehog (Design by Sarah Watts) (Owl)', 2, 1),
    ('4C-YWG4-HK9L', 'B00JBGIZLK', 'Capri Designs- Perpetual Wall Calendar - Hedgehog (Design by Sarah Watts)', 2, 1)
]

sql = """
    INSERT INTO product (`sku`,`asin`,`title`,`user`,`marketplace`)
    VALUES
        ($vals[0][1], $vals[0][2], $vals[0][3], $vals[0][4], $vals[0][5]),
        ($vals[1][1], $vals[1][2], $vals[1][3], $vals[1][4], $vals[1][5]),
        ($vals[2][1], $vals[2][2], $vals[2][3], $vals[2][4], $vals[2][5]),
    ON DUPLICATE KEY UPDATE
        product.`sku` = VALUES(product.`sku`),
        product.`asin` = VALUES(product.`asin`),
        product.`title` = VALUES(product.`title`),
        product.`user` = VALUES(product.`user`),
        product.`marketplace` = VALUES(product.`marketplace`);
"""

db.execute(sql)

[kozlovsky]

If you really want to insert literal values into SQL command, until the bug is fixed you can get native connection to the database and use it to execute the query:

connection = db.get_connection()
cursor = connection.cursor()
cursor.execute(sql)

[lukashambsch]

Thanks for the fix.

I know it's best to use the query parameters. However I'm building the query from a list of dictionaries where the key is the field and the value is the value. All of the dictionaries will have the same fields. Initially I wasn't able to think of a way to use query parameters since the length would be dynamic. But I'll give it another shot.

Currently, I'm escaping the values that are inserted into the sql query to prevent sql injection.

[lukashambsch]

@kozlovsky I've been using this commit 2908838, which fixes this bug. However, I'm seeing the same issue with dollar signs. When I install the 0.7.3 release, I don't see the issue with duplicated dollar signs, but I see this issue with the duplicated percentage signs.

This is an issue that's corrupting our product data. Any ideas on how I can get both dollar signs and percentage signs escaping correctly?

[kozlovsky]

Thanks for reporting, it should be fixed now. I added some tests as well. The issue with dollars is slightly different then the issue with duplicated percentage signs: when using db.execute or db.select, it is necessary to replace each $ in your SQL literals with $$, because $foo means a parameter. If the SQL text has no such parameters, you can just do

sql = sql.replace('$', '$$')
db.execute(sql)