-1

I'm looking to escape special characters in string for Python 2.7.

For example, if I have :

str = "You're the best "dog" on earth."

I would have :

str = "You\'re the best \"dog\" on earth."

I want it because I'm inserting strings in SQL database using pymySQL and I can't find a way to do this.

I guess escaping characters must be like this ? (not really sure) I also would find a way to do the reverse action remove escpaing characters.

Jaky Chane
  • 85
  • 1
  • 2
  • 10
  • [The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/) – deceze Jun 27 '17 at 08:33

2 Answers2

2

You are approaching this entirely the wrong way. You should never need to escape special characters when inserting a string into a SQL database: always use parametrised SQL queries and any needed escaping will be done for you. If you start trying to escape the strings yourself you are opening your code up to all manner of security problems. 

with connection.cursor() as cursor:
    # Create a new record
    sql = "INSERT INTO `mytable` (`thestring`) VALUES (%s)"
    cursor.execute(sql, (str,))

If you ever find yourself building a query string out of data that has come from any outside source stop and reconsider: you should never need to do that.

Duncan
  • 92,073
  • 11
  • 122
  • 156
1

You don't need to escape values for the purpose of SQL by hand! Let the database API take care of that.

  1. Form a valid string literal in Python source code:

    str = "You're the best \"dog\" on earth."
str = 'You\'re the best "dog" on earth.'
str = """You're the best "dog" on earth."""

    These are all equivalent, you just need to escape the appropriate quotes that you're using as string literal terminators.

  2. Use the database API correctly and don't worry about escaping. From the manual:

    sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    Escaping is handled by separating the query and values, not by adding backslashes.

deceze
  • 510,633
  • 85
  • 743
  • 889
  • Hi @deceze, how can I put an existing string between """ """ ? – Jaky Chane Jun 28 '17 at 16:35
  • You tall me @deceze that the best way to escape " or ' is to put the string between """ """ like : str = """You're the best "dog" on earth.""" but how can I insert an existing string extracted from a dict and put it between """ """. It s not work if I do str = "" + str + "". – Jaky Chane Jun 28 '17 at 16:42
  • That makes no sense. The triple quotes only make sense when you initially write the string literal; of you already have the string with the desired contents you don't need to write it again. – deceze Jun 28 '17 at 17:40
  • Ok thanks for your answer, so how can I escape " and ' from existing string to insert it ? @deceze – Jaky Chane Jun 29 '17 at 08:37
  • Uhm… `cursor.execute(query, (value1, value2))`. Don't escape, use prepared statements with placeholders. Please read the article [again] which I posted as comment under the question. – deceze Jun 29 '17 at 08:41