More PostgreSQL incompatibilities [#1253522]

In using this module on our PostgreSQL-based site, we've come across some PostgreSQL incompatibilities. Basically, there are two problems:

1. In some queries, field names (lid and weight) are being quoted with backquotes (`). Quoting a field in backquotes is a MySQLism (if field names need to be quoted in PostgreSQL, you use single quotes (')). Best practice is to avoid the need to quote field names at all (change the field name to something that doesn't conflict with a reserved word, or always use table.field (or table_alias.field) to refer to the field).

2. In a number of places, numeric placeholders (%d) are being surrounded by quotes('). This is not actually needed for security purposes (the %d placeholder itself will ensure that only digits are inserted, so this doesn't open you up to SQL injection), and putting the quotes there changes the paramter to a string. Apparently, this doesn't bother MySQL, but PostgreSQL is stricter about matching types.

Patch to follow.

Comment	File	Size	Author
#1	Correct_PostgreSql_Incompabilities-1253522-1.patch	9.43 KB	ben coleman

Comments

Comment #1

ben coleman commented 18 August 2011 at 19:36

Status:

Active

» Needs review

Status	File	Size
new	Correct_PostgreSql_Incompabilities-1253522-1.patch	9.43 KB

This patch fixes the PostgreSQL compatibilities identified above. Backquotes surrounding SQL fieldnames in selects have been removed, and single quotes surrounding %d placeholders have been removed.

This works on PostgreSQL. Unless lid and weight are MySQL reserved names, I think this should work on MySQL. I left backquoted field names in two of the .install files(node_limit.install and node_limit_interface/node_limit_interval), because you can't do a Node Limit update on a PostgreSQL system anyhow (the ALTER TABLE which changes the limit field to nlimit won't run on PostgreSQL with unquoted field names anyway (limit being a PostgreSQL reserved name), so upgrading to this on a PostgreSQL system will require an uninstall and re-install. I thought it best to leave the .install files in a state where MySQL can run the update procedures.

If MySQL objects to bare field names of lid or weight, either the field names need to be changed, or references to them need to be changed to full table.field references.