History | Log In     View a printable version of the current page.  
Issue Details (XML | Word | Printable)

Key: OX-3634
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: andrew.hill
Reporter: Monique Szpak
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
OpenX Ad Server

No validation rules on the table prefix

Created: 28/Jul/08 09:43 AM   Updated: 17/Feb/09 03:34 PM
Component/s: OXP: Installation & Upgrade System
Affects Version/s: OpenX 2.4.8, Milestone 21, OpenX 2.6.0, OpenX 2.7.10-dev, OpenX 2.7.14-dev, OpenX 2.7.27-beta
Fix Version/s: Milestone 24, OpenX 2.6.3, OpenX 2.7.27-beta, OpenX 2.4.11
Security Level: Public (All users can see these issues)

Time Tracking:
Original Estimate: 4h
Original Estimate - 4h
Remaining Estimate: 0h
Time Spent - 5.33h
Time Spent: 5.33h
Time Spent - 5.33h

File Attachments: 1. Text File badchars_mysql.txt (3 kb)
2. Text File badchars_pgsql.txt (3 kb)

Image Attachments:

1. pgsql_263rc8.jpg
(107 kb)

2. pgsql_263rc8_2.jpg
(99 kb)

3. pgsql_error_message.jpg
(111 kb)
Issue Links:
Depends
 
Reference

Passed QA Version/s: OpenX 2.6.3


 Description  « Hide
The table prefix is not validated for illegal characters, or for characters that require quoting, in the installer.

Even valid characters that require quoting (such as hyphen) will cause errors throught the application due to inconsistant schema object identifier quoting.

Options:

1) disallow any character that requires quoting along with illegal characters using validation rules
2) disallow illegal characters only and fix the inconsistant schema object identifier quoting in the application.



 All   Comments   Work Log   Change History   FishEye   Crucible   Builds      Sort Order: Ascending order - Click to sort in descending order
Matteo Beccati - 06/Aug/08 12:33 PM
After a quick meeting on IRC (Chris, Monique and me), we all agreed that option 2 wasn't viable.

<monique> the quote() thingy is not an option at all
<monique> because for mysql you need to use backticks, and that severely screws up DBC
<monique> I tested it


Applied Mysql checks:
Mysql specification:
http://dev.mysql.com/doc/refman/4.1/en/identifiers.html
Mysql specification: http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

For 4.0, 4.1, 5.0 seem to be the same

  • No identifier can contain ASCII 0 (0x00) or a byte with a value of 255.
  • Before MySQL 4.1, identifier quote characters should not be used in identifiers.
  • Database, table, and column names should not end with space characters.
  • Database and table names cannot contain "/", "\", ".", or characters that are not allowed in filenames.
  • we disallow single and double quotes

Table/Database name maximum length:

  • 64

PGSL checks:
http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

  • SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_).
  • Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($).
  • maximum identifier length is 63
  • we disallow single and double quotes


Sue Houghton - 06/Oct/08 09:14 AM
Fix version changed to 2.6.3 following 2.6.2 security fix release

Sue Houghton - 06/Oct/08 09:23 AM
Fix version updated to 2.4.10 following 2.4.9 release for security fix

Sue Houghton - 14/Oct/08 01:35 PM - edited
Reopening this in 2.6.3-rc1. Seeing the following behaviour in the installer:

Case 1: Prefix contains invalid character (tried ' / ! £)
RESULT: PEAR Error

MDB2 Error: syntax error

_doQuery: [Error message: Could not execute statement]
[Last executed query: CREATE TABLE o£_tmp_dbpriviligecheck (tmp int)]
[Native code: 1064]
[Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '�_tmp_dbpriviligecheck (tmp int)' at line 1]
Error message displayed:

  • Failed to CREATE TABLE - check your database permissions
  • Insufficient database permissions or incorrect database settings to install

Case 2: Valid prefix, database name contains characters -

Example 1

  1. db name = openads' (also for openads\ )
  2. Save changes
    RESULT:
    message displayed
  • Database names cannot contain "/", "\", ".", or characters that are not allowed in filenames
  • Installation failed to create the database openads\'

db name now displayed as openads\'

Example 2

  1. db name = openads"
  2. Save changes
    RESULT:
    message displayed
  • Database names cannot contain "/", "\", ".", or characters that are not allowed in filenames
  • Installation failed to create the database openads\"

db name now displayed as openads\"


Monique Szpak - 21/Oct/08 02:17 PM
A list of ascii characters (0-255) that will cause a Pear Error if found in a MySql tablename (even if quoted).

Monique Szpak - 21/Oct/08 02:18 PM
A list of ascii characters (0-255) that will cause a Pear Error if found in a Postgres tablename (even if quoted).

Monique Szpak - 23/Oct/08 11:42 AM - edited
Fixed in 2.7.27-beta-rc2 only. Please re-test before I port to 2.6 / 2.4

(revision 27830)


Sue Houghton - 24/Oct/08 10:19 AM
Retested in 2.7.27-beta-rc2.

All looking ok on mysql. On pgsql the problems described above have been fixed, but I have one minor point about the error messages displayed. Follow the steps for either example in Case 2. Error message displayed as follows:

  • MDB2 Error: unknown error
  • _doQuery: [Error message: Could not execute statement] [Last executed query: CREATE DATABASE "openads\'" ENCODING 'utf8']
  • Installation failed to create the database openads'

on mysql only the 3rd message on this list is displayed.

Otherwise looking ok!


Monique Szpak - 24/Oct/08 12:00 PM - edited
In 2.7.27-beta-rc3 validation is now as follows:

pgsql and mysql
Table names may not contain any of ! " # % & \' ( ) * + , - . \/ : ; < = > ? @ [
] ^ ` { | } ~ £ nor any non-printing characters

mysql
Table names are limited to 64 characters in length

pgsql
Table names are limited to 63 characters in length
'Table names should not start or end with space characters'
Table names must start with an alphabetic character or underscore


Monique Szpak - 27/Oct/08 04:33 PM
Ported fixes, now fixed in:
2.7.27-beta-rc3
2.6.3-rc7
2.4.10-rc1

Sue Houghton - 29/Oct/08 11:25 AM - edited
also failing in pgsql. See pgsql_263rc8.jpg for result when entering openads" as the database name, and pgsql_263rc8_2.jpg for prefix of ox"_

Nataliya Drabyk - 29/Oct/08 12:52 PM
separate case of Table prefix validation which doesn't work with Cyrillic symbols raised in OX-4344

Monique Szpak - 29/Oct/08 01:05 PM
Postgres display of error and bad values issue fixed in 2.6.3-rc9

Sue Houghton - 30/Oct/08 10:50 AM
Passed retest in 2.6.3-rc9

Sue Houghton - 03/Nov/08 01:40 PM
Added affects version = 2.7.27-beta for bug triage purposes.


Monique Szpak - 04/Nov/08 09:47 AM
Fixed in 2.6.3-rc12

Sue Houghton - 04/Nov/08 12:30 PM
Re-passed in 2.6.3-rc12 for php447 and php525

Sue Houghton - 11/Nov/08 10:48 AM
Closed in 2.7.27-beta-rc6