0

goal

output \" in python.

problems

Suppose we want to generate SQL in python to insert data into a table with json format columns (we'll feed it to the RDBMS later).

When you generate a json string in python, the double quotation is converted to \\". However, in the json format, escaped double quotation is represented as \". As it is, SQL generated in python cannot be directly applied to an RDBMS.

But, in python, single back slash (\) is expressed by \\, and escaped double quotation (\") is expressed by \\". If create string object like \", python will interpret it as ".

What do you think is the best way to output \" in python?

puruneko
  • 23
  • 3
  • Show us how you are generating the SQL at the moment! – Klaus D. Feb 11 '21 at 05:36
  • Use a raw string: `r'\"'` – ddejohn Feb 11 '21 at 05:37
  • generated SQL goes like `INSERT INTO table (id, content) VALUES (nextval('seq_draft_id'), '[{"name": "abc", "message": "abc\\"def\\"ghi"}]')` – puruneko Feb 11 '21 at 05:48
  • `r'\"'` will be interpreted as `\"` in python, but the output will be `\\"` – puruneko Feb 11 '21 at 05:51
  • @metatoaster thanks! but on python3.7, `json.dumps('a"bc')` outputs `'a\\"bc'`. – puruneko Feb 11 '21 at 05:54
  • Try wrapping the statement using `print`, as what you are seeing is the `repr` version of the string. In any case, please look into making use of parameterized SQL queries (as [an example](https://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/)) to correctly construct SQL statements from untrusted user input. Making use of naive escaping mechanism and simple escaping of quotes like what you are trying to do is insufficient, as it may be possible to launch an attack using a different charset. – metatoaster Feb 11 '21 at 06:02
  • As an example, PHP completely fails at solving this problem, as they use the query escape method to sanitize SQL, rather than using parameterized queries; [more details in this article](https://cmsdk.com/mysql/sql-injection-that-gets-around-mysqlrealescapestring.html), also [this one](http://www.unixwiz.net/techtips/sql-injection.html) which was linked from article in the previous comment. – metatoaster Feb 11 '21 at 06:03
  • @metatoaster thanks a lot! On enterprise version, I make sure to use a parameterized SQL. At this time, I created SQL using raw string because converting ORM object to text object. – puruneko Feb 11 '21 at 06:20

2 Answers2

0

I think this can be with raw string:

>>> print(r' \" ')
>>> \"
Elabbasy00
  • 628
  • 9
  • 21
  • `print(r' \" ')` does indeed output the data correctly. However, in file output (`fp.write(' \" ')`), I got wrong (`\\"`). – puruneko Feb 11 '21 at 06:03
0

try this,

>>> s = '\\\"'
>>> print(s)
\"
>>> print('\\\"')
\"

# 
\\ => \
\" => "
beef
  • 1