SQL Parameters - where does expansion happens

Question

I'm getting a little confused about using parameters with SQL queries, and seeing some things that I can't immediately explain, so I'm just after some background info at this point.

First, is there a standard format for parameter names in queries, or is this database/middleware dependent ? I've seen both this:-

DELETE * FROM @tablename

and...

DELETE * FROM :tablename

Second - where (typically) does the parameter replacement happen? Are parameters replaced/expanded before the query is sent to the database, or does the database receive params and query separately, and perform the expansion itself?

Just as background, I'm using the DevArt UniDAC toolkit from a C++Builder app to connect via ODBC to an Excel spreadsheet. I know this is almost pessimal in a few ways... (I'm trying to understand why a particular command works only when it doesn't use parameters)

Poor example: you can't typically use a parameter for the name of the table itself. — Joel Coehoorn, May 21 '13 at 03:58
@JoelCoehoorn - If I'd used a 'better' example, nobody would have told me what my actual problem was! — Roddy, May 21 '13 at 09:03

score 2 · Answer 1 · answered May 21 '13 at 03:53

2

With such data access libraries, like UniDAC or FireDAC, you can use macros. They allow you to use special markers (called macro) in the places of a SQL command, where parameter are disallowed. I dont know UniDAC API, but will provide a sample for FireDAC:

ADQuery1.SQL.Text := 'DELETE * FROM &tablename';
ADQuery1.MacroByName('tablename').AsRaw := 'MyTab';
ADQuery1.ExecSQL;

answered May 21 '13 at 03:53

da-soft

7,670
28
36

Thanks - I just found UniDAC macros. They don't seem to handles quotes in a way that prevents injection attacks, though. – Roddy May 21 '13 at 08:56

score 1 · Answer 2 · answered May 21 '13 at 04:02

Second - where (typically) does the parameter replacement happen?

It doesn't. That's the whole point. Data elements in your query stay data items. Code elements stay code elements. The two never intersect, and thus there is never an opportunity for malicious data to be treated as code.

connect via ODBC to an Excel spreadsheet... I'm trying to understand why a particular command works only when it doesn't use parameters

Excel isn't really a database engine, but if it were, you still can't use a parameter for the name a table.

.. .which explains why I've had trouble trying to 'see' the expanded SQL in order to make sure I was using the parameters correctly : It simply never exists. — Roddy, May 21 '13 at 09:04

score 0 · Accepted Answer · answered May 20 '13 at 10:27

0

SQL parameters are sent to the database. The database performs the expansion itself. That allows the database to set up a query plan that will work for different values of the parameters.

Microsoft always uses @parname for parameters. Oracle uses :parname. Other databases are different.

No database I know of allows you to specify the table name as a parameter. You have to expand that client side, like:

command.CommandText = string.Format("DELETE FROM {0}", tableName);

P.S. A * is not allowed after a DELETE. After all, you can only delete whole rows, not a set of columns.

answered May 20 '13 at 10:27

Andomar

232,371
49
380
404

Thanks. The `DELETE *` does seem to work, but I understand it's not necessary. I thought primary advantage of params was to avoid SQL injections - so it seems odd that table names aren't supported? – Roddy May 20 '13 at 10:40
1

Parameters were created for performance. They allow the database to prepare for identical queries with different parameter values. If they were designed against injection, they would have allowed table names, columns after `order by`, and so on. – Andomar May 20 '13 at 10:46
Performance is a nice side-effect. Parameters were created for _security_, to protect against sql injection attacks in an absolutely inviolate way. – Joel Coehoorn May 21 '13 at 04:01
@JoelCoehoorn - so why can't they be used in table names, for instance? Surely that's bit of an omission? – Roddy May 21 '13 at 08:53
Most references I've found to parameters/prepared statements list both performance and protection as primary advantages, without implying either was a side-effect. – Roddy May 21 '13 at 09:06
@JoelCoehoorn: If the goal was protection against SQL Injection, that could have been done client side. You wouldn't have to rewrite the query optimizer (one of the most complex parts of a database) – Andomar May 21 '13 at 09:09
@Roddy - sql queries are "compiled" by sql server before execution, to ensure correctness and validate permissions. If the table name changes at run time, sql server can't validate those permissions as effectively. – Joel Coehoorn May 21 '13 at 13:54

SQL Parameters - where does expansion happens

3 Answers3