Question-mark parameter markers

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

A "parameterized" SQL request is when you specify a question-mark parameter marker in place of an expression within the SQL request text. Parameter markers are recommended over SQL literals for a couple of reasons.

  1. Because the SQL request text stays the same as the SQL request is executed multiple times, the Teradata Database statement cache hit ratio can be higher, resulting in better performance.
  2. They help protect against SQL injection attacks.

But application developers often wonder where question-mark parameter markers can be used in a SQL request. For example, if we try to use a question-mark parameter marker in a GRANT statement:

connection.prepareStatement("grant select on mytable to ?");

then the Teradata Database would reject the request with an error:

[Teradata Database] [TeraJDBC 14.00.00.08] [Error 3707] [SQLState 42000] Syntax error,
expected something like a name or a Unicode delimited identifier between the 'to'
keyword and '?'.

A general rule of thumb is that the Teradata Database only supports question-mark parameter markers for certain syntax elements within DML statements: SELECT, INSERT, UPDATE, DELETE, MERGE, SET QUERY_BAND FOR TRANSACTION, etc.

A question-mark parameter marker can substitute for a data value, but cannot substitute for a database object name (table name, column name, user name, etc.)

Because of that restriction, the Teradata Database doesn't support question-mark parameter markers at all for DDL and DCL statements: CREATE, DROP, GRANT, ALTER, MODIFY, etc.