-5

I have a mysql table, which stores some sql queries.

The schema is like:

CREATE TABLE `queries` (`qry_id` int(11) NOT NULL AUTO_INCREMENT,
`qry_text` varchar(2000) COLLATE utf8_unicode_ci NOT NULL,
`result_table` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`last_run` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`period` int(11) DEFAULT NULL,
`error` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`qry_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

For example, one qry text is SELECT * FROM TABLE WHERE COL1 = '%s' and COL2 = '%s'

In python, I pulled a str called qry from a sql table.

qry = "SELECT * FROM TABLE WHERE COL1 = '%s' and COL2 = '%s' "

What I want is:

qry = """SELECT * FROM TABLE WHERE COL1 = '%s' and COL2 = '%s' """ % (var1, var2)

How to artificially add triple quotes around qry and append % (var1, var2) to it. I need to pass qry (in the format I specified) to an API to run. Thanks!

tonystarkix
  • 77
  • 2
  • 11
  • 2
    You do know `""` is exactly the same as `""""""` – Jakob Bowyer Mar 24 '13 at 21:51
  • Tripple quotes are just a syntax to form strings. They are not part of the final produced string. – Martijn Pieters Mar 24 '13 at 21:51
  • I don't understand the question. What exactly is in the sql table? Does it contain a query: `SELECT * FROM TABLE WHERE COL1 = '%s' and COL2 = '%s'`? A python code fragment: `qry = "SELECT * FROM TABLE WHERE COL1 = '%s' and COL2 = '%s' "`? – Eric Mar 24 '13 at 21:51
  • 1
    Also, sanatise your input. lest we forget little bobby droptables. http://xkcd.com/327/ – Jakob Bowyer Mar 24 '13 at 21:52
  • You store `'qry = "SELECT * FROM TABLE WHERE COL1 = '%s' and COL2 = '%s' "'` in you database. o_O (@JakobBowyer it's Bobby Tables …) – Kijewski Mar 24 '13 at 21:53
  • @Kay I store all kind of things in my database – Jakob Bowyer Mar 24 '13 at 21:55
  • @JakobBowyer that's alright as long as you don't intent to eval that strings. That question reads like OP is about to. – Kijewski Mar 24 '13 at 21:57
  • @Eric This is the table schema: CREATE TABLE `queries` ( `qry_id` int(11) NOT NULL AUTO_INCREMENT, `qry_text` varchar(2000) COLLATE utf8_unicode_ci NOT NULL, `result_table` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL, `last_run` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `period` int(11) DEFAULT NULL, `error` tinyint(1) DEFAULT NULL, PRIMARY KEY (`qry_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; I pulled qry_text from this table and name it as qry – tonystarkix Mar 24 '13 at 21:58
  • @Kay its not my question dude. – Jakob Bowyer Mar 24 '13 at 21:58
  • @tonystarkix: Add that to the question so that someone stands a chance of being able to read it. – Eric Mar 24 '13 at 22:05

1 Answers1

0

I don't understand your problem:

def get_dangerous_query():
    # do some database access
    # For a demo, just return what we know the table contains
    return "SELECT * FROM TABLE WHERE COL1 = '%s' and COL2 = '%s'"

qry = get_dangerous_query() % (var1, var2)

""" vs " only has an effect on string literals. Quotes are completely meaningless if a string comes from an external source.

Eric
  • 95,302
  • 53
  • 242
  • 374