I'm developing an application in AIR via Flex, but I'm not seeing where I'm going wrong with SQLite (I'm used to MySQL). Parameters work, but only in certain instances. Is this part of the built-in sanitation system against sql injection? Thanks for any help!
Works:
sqlite
"INSERT :Fields FROM Category", where the parameter is :Fields = "*"
as3
var statement:SQLStatement = new SQLStatement();
statement.connection = connection;
statement.text = "INSERT :Fields FROM Category";
statement.parameters[":Fields"] = "*";
statement.execute;
Doesn't Work (SQL syntax error at ":Table"):
sqlite
"INSERT :Fields FROM :Table", where the parameters are :Fields = "*" and :Table = "Category"
as3
var statement:SQLStatement = new SQLStatement();
statement.connection = connection;
statement.text = "INSERT :Fields FROM :Table";
statement.parameters[":Fields"] = "*";
statement.parameters[":Table"] = "Category";
statement.execute;
Generally one cannot use SQL parameters/placeholders for database identifiers (tables, columns, views, schemas, etc.) or database functions (e.g., CURRENT_DATE), but instead only for binding literal values.
With server-side support for parameterized (a.k.a. prepared) statements, the DB engine parses your query once, remembering out the peculiars of any parameters -- their types, max lengths, precisions, etc. -- that you will bind in subsequent executions of the already-parsed query. But the query cannot be properly parsed into its syntactic elements if critical bits, like database objects, are unknown.
So, one generally has to substitute table names oneself, in a stored procedure or in client code which dynamically concats/interpolates/whatevers the SQL statement to be properly executed. In any case, please remember to use your SQL API's function for quoting database identifiers, since the API won't do it for you.
Not sure if this is the same but I ran across something similar in Java. Basically you can't add a table as a parameter so you must generate the statement like so:
var statement:SQLStatement = new SQLStatement();
statement.connection = connection;
statement.text = stringUtil.substitute("INSERT :Fields FROM {0}", "Category");
statement.parameters[":Fields"] = "*";
statement.execute;
This is mostly likely not the securest solution, so you might want to some custom validation of the data before you add the table name.. so someone doesn't try to send it the table name ";drop tableName..."
