thekid/rfc — Issue #163: RFC 0163: Respect ANSI SQL in rdbms package
Author: thekid
Created: 2011-10-09 15:30:54+00:00
State: open

Scope of Change
====
All rdbms drivers will be made more ANSI SQL compatible by preferring
single quotes as string delimiter over double quotes.

Rationale
====
Some databases can be configured to explicitely support ANSI mode
where double quotes are an indicator for quoted identifiers rather
than strings.
Thus, changing to use ANSI SQL will increase compatibility of the
XP framework's rdbms API to as many systems as possible.

Functionality
====
Change to the rdbms systems's respective SQL dialect class.

Security considerations
====
String escaping needs to be adjusted appropriately to still effectively
suppress SQL injection.

Speed impact
====
n/a

Dependencies
====

Related documents
====
- http://dev.mysql.com/doc/refman/5.1/en/ansi-mode.html
Running MySQL in ANSI mode

- http://xp-framework.net/rfc/contrib/rfc0163.diff
Implementing patch

Comments
====
friebe, Sat May 31 18:27:53 2008
----
Implemented after initial commit: Support for string literals.

For example, this call `$q= $conn->query('select "Hello"');` will
be transformed to `select 'Hello'` in MySQL (since that uses the
single quote as string delimiter).

Unclosed strings will lead to an SQLStateException:

```php
query('select "Hello'); // *BLAM*
?>
```

Unknown tokens outside of strings will lead to an SQLStateException:

```php
query('select %X', 'String'); // *BLAM*
$q= $conn->query('select "%X"'); // OK, inside a string
?>
```

Percent signs in strings still need to be double escaped:

```php
query('select "%%"'); // select '%'
$q= $conn->query('select "%%X"'); // select '%X'
?>
```

Quote signs inside strings are escaped by double-quoting:

```php
query('select """Hello"""'); // select '"Hello"'
$q= $conn->query('select %s', '"Hello"'); // select '"Hello"'
?>
```

The necessity to escape `%` signs in strings is based solely on backwards
compatibility reasons - it could be removed instantaneously! This behavior
is inconsistent, though - `$q= $conn->query('select "%"');`> will
also work. TODO: Deprecate this in the next major release (5.7.0?)