Portable SQL/Common/Aliases

Alias for tables and columns

Don't use the keyword "AS" between 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

Quotes and numeric values

MS SQL Server and Sybase ASE doesn't allow single quotes (') around numeric values (integer and floating point values), for example

INSERT INTO table (floatval,string) VALUES ('-3.14','some text')

generates an error when using these servers. They only accept

INSERT INTO table (floatval,string) VALUES (-3.14,'some text')

This gets tricky if the values are supplied by a user. You must validate the input to be sure it is a numeric value and you can't use the escape() function. Otherwise, if you don't look at the input, your application will be vulnerable to SQL injection and you will be in deep trouble.

---

Back to Overview