Skip to main content
Stack Overflow
  1. About
  3. For Teams

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

Variable table name in sqlite

I'm working on a project right now that catalogs data from a star simulation of mine. To do so I'm loading all the data into a sqlite database. It's working pretty well, but I've decided to add a lot more flexibility, efficiency, and usability to my db. I plan on later adding planetoids to the simulation, and wanted to have a table for each star. This way I wouldn't have to query a table of 20m some planetoids for the 1-4k in each solar system.

I've been told using string constructors is bad because it leaves me vulnerable to a SQL injection attack. While that isn't a big deal here as I'm the only person with access to these dbs, I would like to follow best practices.

Currently I'm doing this:

cursor.execute("CREATE TABLE StarFrame"+self.name+" (etc etc)")

This works, but I would like to do something more like:

cursor.execute("CREATE TABLE StarFrame(?) (etc etc)",self.name)

though I understand that this would probably be impossible. though I would settle for something like

cursor.execute("CREATE TABLE (?) (etc etc)",self.name)

Is it possible to use a variable as your table name without having to use string constructors to do so?

Answer*

Draft saved
Draft discarded
Cancel
4
  • 25
    Personally, I'd throw an exception if such characters are seen instead. They must not be there, so something is wrong and I'd rather know about it. Commented May 4, 2011 at 6:18
  • 3
    -1; the details of this answer seem flawed. It's overly restrictive (not allowing, for instance, underscores in table names, which are fairly common) and, as @JanHudec has pointed out, if scrub() ever actually removes any characters, it's almost certainly going to have broken the query; that should cause an exception to be thrown immediately rather than waiting for query execution time to find out. Commented Apr 18, 2017 at 22:23
  • 2
    The issue is that parameters can only substitute in values, not other identifiers or general bits of SQL. That means that they're trivial to inject into the bytecoded version of the query. Substituting more generally would require recompiling the query (yes, even for just changing table or column names) so the SQLite engine says "no", and you need to do it in the outer language. Commented Sep 30, 2018 at 13:39
  • 1
    In my mind, the real question is why the end user has input into the names of tables used in the database. Perhaps there's a better way to normalize OP's database. Commented Jan 9, 2023 at 4:23

default

AltStyle によって変換されたページ (->オリジナル) /