Portable SQL/Common/Aliases

Using aliases

Aliasing tables

Don't use the keyword "AS" between the table name and alias as no DBMS requires it and some don't recognize it. Instead, always write:

SELECT * FROM table t WHERE t.id = 0

Aliasing columns

Contrary to "Aliasing tables" the keyword "AS" between column name and alias is required by most database servers and should always be present:

SELECT count(*) AS cnt FROM table

Aliases for computed columns

The naming of computed columns implemented by the database servers is very different. Some name it like the computational expression ('count(*)'), most often in upper case and sometimes it isn't named at all. Therefore computed columns should be always given a name:

SELECT MAX(id) AS maxid FROM table

Quoting in statements

Quoting values

Quoting string values which should be used inside an SQL statement is essential. But what about numerical values like integers, decimal and floating point values? Many database server support this but MS SQL Server and Sybase ASE don't. Single quotes around numerical values can also cause a performance hit (exceptions seems to be older MySQL servers) and therefore they should be avoided. The correct usage is:

SELECT * FROM table WHERE intcol=1 AND strcol='test'

To avoid being vulnerable to SQL injection attacks, special care must be taken for numerical values from untrusted input sources. Untrusted input is everything that you haven't hard-coded into your application which includes values fetched from the database. Passing them to the escape function of the database library doesn't prevent attacks because as long as no single quote appears somewhere in the value provided by the attacker, the statement will still be valid. If e.g. "1 OR intcol=2" is provided as input for the intcol value of the statement above, this will result in

SELECT id FROM table WHERE intcol=1 OR intcol=2 AND strcol='test'

The statement will be executed by the database server correctly but the meaning has totally changed. Therefore, it's important to do some kind of type checking for the input values if they are provided as character strings. The easiest way is to use the strtol(), strtoll() or strtod() functions provided by the C library:

char* temp;
strtol( input, &temp, 10 );
if( *temp != 0 ) { /* attack detected */ }

If the temp pointer doesn't point to the zero character of the end of the input string afterwards, then the application is sure that the input can't be used without risking serious harm.

---

Back to Overview