#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

  • 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

     
  • Peter "mash" Morgan

    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..

     
  • Peter "mash" Morgan

    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

     
  • Peter "mash" Morgan

    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

     
  • Peter "mash" Morgan

    • assigned_to: nobody --> nobody
    • priority: 5 --> 5
    • labels: 556733 --> 556733
    • status: open --> open
     
  • Peter "mash" Morgan

    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

     
  • Peter "mash" Morgan

    • labels: 556733 --> DB-CORE
    • priority: 5 --> 9
    • assigned_to: nobody --> mashmorgan
    • status: open --> open-accepted
     
  • Peter "mash" Morgan

    • summary: Won't allow field named "delete" --> Reserved words not escaped.. CORE BUG
     
  • Peter "mash" Morgan

    Fixed in SVN/trunk

     
  • Peter "mash" Morgan

    • status: open-accepted --> closed-fixed
     
  • Peter "mash" Morgan

    Fixed in SVN/trunk

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks