Pass list of strings into raw sql query (Python/Django)

Question

I need to pass a dynamic list of strings into raw sql query. Here is my code:

myList = ['PREFIX\000\000923', 'PREFIX\000\000CS3'] # <- strings I have troubles with
myList = ['OK1', 'OK2'] # <- ok strings
myTuple = tuple(myList)
query = "SELECT * FROM public.items WHERE name IN {}".format(myTuple)
result = cursor.execute(query, myTuple)
rows =  dict_fetch_all(cursor)
for row in rows:
    print(row)

The above piece of code works just fine. However, there is a problem with strings with special characters with backslashes like this: "PREFIX\000\000923". What is the right way to code it?

[EDIT] Here is the printed query in the console:

SELECT * FROM public.items WHERE name IN ('PREFIX\x00\x00923', 'PREFIX\x00\x00CS3')

As you see, myList items have been converted to some strange string.

What do you mean by problem? Can you be more specific with the error? — Ahsun Ali, Sep 20 '19 at 12:10
Here is the printed query in the console: SELECT * FROM public.items WHERE name IN ('PREFIX\x00\x00923', 'PREFIX\x00\x00CS3'). As you see, myList items have been converted to some strange string. — imnotafred, Sep 20 '19 at 12:20
Why are you using raw queries for this anyway? Why not `Item.objects.filter(name__in=myList)`? — Daniel Roseman, Sep 20 '19 at 13:16
You can still use models - see model option managed = False. https://docs.djangoproject.com/en/2.2/ref/models/options/#managed — ElmoVanKielmo, Sep 21 '19 at 14:19

Ahsun Ali · Accepted Answer · 2019-09-20T14:00:37.417

-1

Backslash is a special charachter in python.

Putting one backslash followed by any other charachter means some other character in the final string as it is happening in your case.

In order to make sure that your final string contains a backslash and not any strange character you need to put two consecutive backslash symbols as following.

This will do exactly what you want. You can read more about this here.

Also to remove the confusion about dobule backslashes in the final string, that is just a visual misunderstanding as you can see below, using two backslashes do not result in two backslashes in the final string.

Second mistake in the code above is use of tuple function which returns a tuple object instead of a string enclosed form of the list. Following code should work for you.

def customTuple(arr):
    arr = ["'"+item+"'" for item in arr]
    s = '('+','.join(arr)+')'
    return s
myList = ['PREFIX\\000\\000923', 'PREFIX\\000\\000CS3'] # <- strings I have troubles with
myTuple = customTuple(myList)
query = "SELECT * FROM public.items WHERE name IN {}".format(myTuple)
print(query)

edited Sep 20 '19 at 14:00

answered Sep 20 '19 at 12:20

Ahsun Ali

314
1
6

I know about this, but did you check this code? I've checked and it doesn't work. Here is the printed query from the console: SELECT * FROM public.items_view WHERE name IN ('PREFIX\\000\\000923', 'PREFIX\\000\\000CS3'). As you can see, now I have double backslashes and I don't receive any data from DB. Also, the list is not static, but dynamic. I would need to format all the string in the list. – imnotafred Sep 20 '19 at 12:25
@ImNotAfred Please have a look at the updated answer and also if possible have a look at https://stackoverflow.com/questions/301068/quoting-backslashes-in-python-string-literals . These both things prove that the problem is not with the backshashes and probably some where else in the code. Perhaps if you could share any other errors then we might be able to help better. – Ahsun Ali Sep 20 '19 at 12:31
But I don't have any errors. String just doesn't match the data in POSTGRESQL database. I passed ('PREFIX\\000\\000923', 'PREFIX\\000\\000CS3'), but in database there are values: PREFIX\000\000923 and PREFIX\000\000CS3 which are completly different strings. – imnotafred Sep 20 '19 at 12:35
I updated the answer, the problem was with your use of tuple function. @ImNotAfred – Ahsun Ali Sep 20 '19 at 12:44
3

While the explanation of special meaning of backslash is correct, SQL queries must not be created by formatting string with variables. This is vulnerable to SQL injection. Use parametrized queries instead. – ElmoVanKielmo Sep 20 '19 at 12:50
Thank you Ahsun Ali, but when I execute your code above: result = cursor.execute(query, myTuple) I receive an error: "django.db.utils.ProgrammingError: syntax error at or near "\"" – imnotafred Sep 20 '19 at 12:55
@ImNotAfred oh I am sorry, that was because of the missing quotes around the items in array. I've updated the code again, please let me know if it works now. – Ahsun Ali Sep 20 '19 at 14:01
Would really appreciate if you could accept the answer in case it works for you. :) – Ahsun Ali Sep 20 '19 at 14:09
Sure! Currently, I am out of office, but after I check if it works or not, I will give you proper feedback. Thanks for your help! – imnotafred Sep 20 '19 at 16:34
Sure, ignore SQL injection and get your data leaked or deleted https://www.netsparker.com/blog/web-security/sql-injection-cheat-sheet/ – ElmoVanKielmo Sep 24 '19 at 11:11

Pass list of strings into raw sql query (Python/Django)

1 Answers1