Sections

Tuesday, December 18, 2012

PostgreSQL basics 2: SQL

In the previous post, , we installed PostgreSQL on our openSUSE 12.2 system and did the absolute minimum necessary to get it running. We also learned how to run psql, the "terminal-based front-end to PostgreSQL".

PostgreSQL recognizes certain constants (strings, bit strings, and numbers) as "implicitly typed". In the previous example, 257 was recognized as an integer. In this example, we introduce a string constant. Note the single quotes:

backslash_quote (enum)
This controls whether a quote mark can be represented by \' in a
string literal. The preferred, SQL-standard way to represent a quote
mark is by doubling it ('') but PostgreSQL has historically also
accepted \'. However, use of \' creates security risks because in some
client character set encodings, there are multibyte characters in which
the last byte is numerically equivalent to ASCII \. If client-side code
does escaping incorrectly then a SQL-injection attack is possible. This
risk can be prevented by making the server reject queries in which a
quote mark appears to be escaped by a backslash.

Including a single-quote in a string constant, The Right Way: two consecutive single-quotes: