Adding single quotes in string around curly braces - Python

Question

When I run the following:

table = cfg_pre + '_' + cfg_tbl
check_data = 'SELECT COUNT(*) FROM X WHERE TABLE_NM = {}'.format(table)

I get the following:

SELECT COUNT(*) FROM X WHERE TABLE_NM = ABC_123

What I need is:

SELECT COUNT(*) FROM X WHERE TABLE_NM = 'ABC_123'

Otherwise the SQL won't execute. Any ideas?

You shouldn't use string formatting to create SQL queries. Leaves you vulnerable to SQL injection. — user3483203, Jun 07 '18 at 21:24
Let the DB driver of your choice prepare your statement, but in general: `"whatever '{}' else".format("foo bar")` will result in `whatever 'foo bar' else`. — zwer, Jun 07 '18 at 21:26
https://stackoverflow.com/questions/10950362/protecting-against-sql-injection-in-python — user3483203, Jun 07 '18 at 21:27
I'm assuming you want to execute those SQL statements. If you really only want to generate a string with single quotes around that value, let me know and I'll reopen. Or find a more suitable dupe. — Aran-Fey, Jun 07 '18 at 21:30
Even though you don't want to do this for this particular case, the basic question of how to put quotes inside strings is still legitimate, and it deserves an answer. Read [Strings](https://docs.python.org/3/tutorial/introduction.html#strings) in the tutorial, but the basic idea is: you can use any of `'`, `"`, `'''`, or `"""` to quote strings, so you can write `s = "this is 'fine'"`. In the rare cases where that isn't enough, you have to backslash-escape your quote characters: `s = 'this is \'also fine\''`. — abarnert, Jun 07 '18 at 21:40

score 3 · Answer 1 · answered Jun 07 '18 at 21:26

Use double speech marks instead of single speech marks:

"SELECT COUNT(*) FROM X WHERE TABLE_NM = '{}'".format(table)

This isn't entirely relevant (more of a nicety than necessity), but you could also use f-strings if you're using Python3.6 (but you still need the double speech marks).

score 1 · Answer 2 · answered Jun 07 '18 at 21:26

1

If you're using Python 3.6, f-strings are a f-un solution. Ultimately, you need to use double quotes to enclose the single quotes.

table = f'{cfg_pre}_{cfg_tbl}'
check_data = f"SELECT COUNT(*) FROM X WHERE TABLE_NM = '{table}'"

answered Jun 07 '18 at 21:26

bphi

3,115
3
23
36

1

While yes, this achieves the desired output, this is *not* a safe way to generate queries. – user3483203 Jun 07 '18 at 21:27
1

f-strings are cool, but also irrelevant to this problem. What actually fixes things in your answer is using double quotes on the string literal, so you can use single quotes inside the string—exactly the same as Adi219's answer, except that he explains it, while you explain something irrelevant. – abarnert Jun 07 '18 at 21:28
OP didn't ask how to create safe dynamic queries, he asked how to add single quotes. – bphi Jun 07 '18 at 21:28
1

Giving bad advice just because someone asks for bad advice is still bad advice. – user3483203 Jun 07 '18 at 21:31
But it's not just about what the OP wants ... it's about achieving what the OP wants with the best security/efficiency/optimacy/etc. – Adi219 Jun 07 '18 at 21:31

score 1 · Answer 3 · answered Jun 07 '18 at 21:28

While SQL injection is a problem you could run into if you create your query with variables, the solution would be to escape the characters or change quotes.

table = cfg_pre + '_' + cfg_tbl
check_data = "SELECT COUNT(*) FROM X WHERE TABLE_NM = '{}'".format(table)

Or

table = cfg_pre + '_' + cfg_tbl
check_data = 'SELECT COUNT(*) FROM X WHERE TABLE_NM = \'{}\''.format(table)

Adding single quotes in string around curly braces - Python

3 Answers3