#43 Reserved words not `escaped`.. CORE BUG

closed-fixed
DB-CORE (5)
9
2010-11-13
2010-10-16
Jan Zumwalt
No

Attempting to name a field named "delete" will cause a crash.

While creating a user privilege system where I wanted a field named "delete" that allows a user to have that privilege, it crashes every time I attempt to add that field name to a table called "user".

Discussion

1 2 > >> (Page 1 of 2)
  • 3ICE
    3ICE
    2010-10-20

    Because "DELETE" is a reserved keyword in SQL along with the following 337 others:
    ABSOLUTE, ACTION, ADD, AFTER, ALL, ALLOCATE, ALTER, AND, ANY, ARE, ARRAY, AS, ASC, ASENSITIVE, ASSERTION, ASYMMETRIC, AT, ATOMIC, AUTHORIZATION, AVG, BEFORE, BEGIN, BETWEEN, BIGINT, BINARY, BIT, BIT_LENGTH, BLOB, BOOLEAN, BOTH, BREADTH, BY, CALL, CALLED, CASCADE, CASCADED, CASE, CAST, CATALOG, CHAR, CHARACTER, CHARACTER_LENGTH, CHAR_LENGTH, CHECK, CLOB, CLOSE, COALESCE, COLLATE, COLLATION, COLUMN, COMMIT, CONDITION, CONNECT, CONNECTION, CONSTRAINT, CONSTRAINTS, CONSTRUCTOR, CONTAINS, CONTINUE, CONVERT, CORRESPONDING, COUNT, CREATE, CROSS, CUBE, CURRENT, CURRENT_DATE, CURRENT_DEFAULT_TRANSFORM_GROUP, CURRENT_PATH, CURRENT_ROLE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_TRANSFORM_GROUP_FOR_TYPE, CURRENT_USER, CURSOR, CYCLE, DATA, DATE, DAY, DEALLOCATE, DEC, DECIMAL, DECLARE, DEFAULT, DEFERRABLE, DEFERRED, DELETE, DEPTH, DEREF, DESC, DESCRIBE, DESCRIPTOR, DETERMINISTIC, DIAGNOSTICS, DISCONNECT, DISTINCT, DO, DOMAIN, DOUBLE, DROP, DYNAMIC, EACH, ELEMENT, ELSE, ELSEIF, END, EQUALS, ESCAPE, EXCEPT, EXCEPTION, EXEC, EXECUTE, EXISTS, EXIT, EXTERNAL, EXTRACT, FALSE, FETCH, FILTER, FIRST, FLOAT, FOR, FOREIGN, FOUND, FREE, FROM, FULL, FUNCTION, GENERAL, GET, GLOBAL, GO, GOTO, GRANT, GROUP, GROUPING, HANDLER, HAVING, HOLD, HOUR, IDENTITY, IF, IMMEDIATE, IN, INDICATOR, INITIALLY, INNER, INOUT, INPUT, INSENSITIVE, INSERT, INT, INTEGER, INTERSECT, INTERVAL, INTO, IS, ISOLATION, ITERATE, JOIN, KEY, LANGUAGE, LARGE, LAST, LATERAL, LEADING, LEAVE, LEFT, LEVEL, LIKE, LOCAL, LOCALTIME, LOCALTIMESTAMP, LOCATOR, LOOP, LOWER, MAP, MATCH, MAX, MEMBER, MERGE, METHOD, MIN, MINUTE, MODIFIES, MODULE, MONTH, MULTISET, NAMES, NATIONAL, NATURAL, NCHAR, NCLOB, NEW, NEXT, NO, NONE, NOT, NULL, NULLIF, NUMERIC, OBJECT, OCTET_LENGTH, OF, OLD, ON, ONLY, OPEN, OPTION, OR, ORDER, ORDINALITY, OUT, OUTER, OUTPUT, OVER, OVERLAPS, PAD, PARAMETER, PARTIAL, PARTITION, PATH, POSITION, PRECISION, PREPARE, PRESERVE, PRIMARY, PRIOR, PRIVILEGES, PROCEDURE, PUBLIC, RANGE, READ, READS, REAL, RECURSIVE, REF, REFERENCES, REFERENCING, RELATIVE, RELEASE, REPEAT, RESIGNAL, RESTRICT, RESULT, RETURN, RETURNS, REVOKE, RIGHT, ROLE, ROLLBACK, ROLLUP, ROUTINE, ROW, ROWS, SAVEPOINT, SCHEMA, SCOPE, SCROLL, SEARCH, SECOND, SECTION, SELECT, SENSITIVE, SESSION, SESSION_USER, SET, SETS, SIGNAL, SIMILAR, SIZE, SMALLINT, SOME, SPACE, SPECIFIC, SPECIFICTYPE, SQL, SQLCODE, SQLERROR, SQLEXCEPTION, SQLSTATE, SQLWARNING, START, STATE, STATIC, SUBMULTISET, SUBSTRING, SUM, SYMMETRIC, SYSTEM, SYSTEM_USER, TABLE, TABLESAMPLE, TEMPORARY, THEN, TIME, TIMESTAMP, TIMEZONE_HOUR, TIMEZONE_MINUTE, TO, TRAILING, TRANSACTION, TRANSLATE, TRANSLATION, TREAT, TRIGGER, TRIM, TRUE, UNDER, UNDO, UNION, UNIQUE, UNKNOWN, UNNEST, UNTIL, UPDATE, UPPER, USAGE, USER, USING, VALUE, VALUES, VARCHAR, VARYING, VIEW, WHEN, WHENEVER, WHERE, WHILE, WINDOW, WITH, WITHIN, WITHOUT, WORK, WRITE, YEAR, ZONE

     
  • Jan Zumwalt
    Jan Zumwalt
    2010-10-20

    I created the field using php and it works fine (as does other reserved words such as "YEAR"). This is a bug with SQLITE-PRO and not a constarint of Sqlite :)

     
  • 3ICE
    3ICE
    2010-10-21

    What a mess that is... People are allowed to use reserved names.

    When you are deleting users without delete privileges...
    DELETE FROM user
    WHERE delete = 0 // Warning: nonsense

    The column name you want to use is CanDelete (boolean), or Permissions (bitfield)

     
  • Jan Zumwalt
    Jan Zumwalt
    2010-10-21

    If we are to believe that reserved words are not to be used, then imagine Oracle or Mysql accounting databases where 'time', 'year', 'hour', 'day', etc are not allowed. Or manuafacturing plants that can not use 'start', 'release', 'length', 'first', 'last', 'filter', 'count', 'full', etc. As for delete consider: DELETE FROM 'user'
    WHERE 'delete' = 'yes' // Makes sense
    or

    DELETE FROM 'car'
    WHERE 'year' >= '1995' // Makes sense

     
  • There's a bug in sqlitebrowser, all fields, tables etc should be escaped but arent (ie placeholders)

    eg
    delete from 'upper' where 'delete' is null

    Am trying to get access from the developer to fix some of these trivial bugs..

     
  • I have fixed the part of >>
    create field named `delete` using `backticks`... but ahhh the worms have come out..

    even
    select * from foo where delete=1 is now problems...

    So in summary, there is a flaw in that "objects" are not espaced..
    escape techiques are
    select [delete] from [update]
    select "delete" from "update" or
    select `delete`from `update`.

    Need to try and figure out if this bug is worth fixing before rewrite ?? ummmm

    mash

     
  • Now marked this as a "core" issue...

    The fix is to actually find some "mechanism" to escape and construct queries in current code....
    eg sql.append("create table")...

    before
    createStatement.append(item->text(0));
    after
    createStatement.append("`").append(item->text(0)).append("`");

    So a query builder is what we need.. but that's in QtSQL aleady probably ?
    ie placeholders and let qt do it for us maybe.. or
    We stick to the c/c++ interface of sqlitebroser ?
    or ??

    Feedback welcome please

     
    • assigned_to: nobody --> nobody
    • priority: 5 --> 5
    • labels: 556733 --> 556733
    • status: open --> open
     
  • Now marked this as a "core" issue...

    The fix is to actually find some "mechanism" to escape and construct queries in current code....
    eg sql.append("create table")...

    before
    createStatement.append(item->text(0));
    after
    createStatement.append("`").append(item->text(0)).append("`");

    So a query builder is what we need.. but that's in QtSQL aleady probably ?
    ie placeholders and let qt do it for us maybe.. or
    We stick to the c/c++ interface of sqlitebroser ?
    or ??

    Feedback welcome please

     
    • labels: 556733 --> DB-CORE
    • priority: 5 --> 9
    • assigned_to: nobody --> mashmorgan
    • status: open --> open-accepted
     
1 2 > >> (Page 1 of 2)