/ Check-in [73a6b8c1]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Experimental changes to (optionally) allow double-quoted strings to be checked against known identifiers.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | normalize_v4
Files: files | file ages | folders
SHA3-256: 73a6b8c1b9c282b9d28c2ce131fc2f3545aaef8b9357a4ae17b46059e473c2d6
User & Date: mistachkin 2018-12-06 20:18:43
Context
2018-12-06
20:18
Experimental changes to (optionally) allow double-quoted strings to be checked against known identifiers. Leaf check-in: 73a6b8c1 user: mistachkin tags: normalize_v4
17:06
When masking bits off of sqlite3.flags, make sure the mask is 64 bits in size so as not to accidentally mask of high-order bits. check-in: 53d3b169 user: drh tags: trunk
02:01
Remove the unused pColHash field from the Table object. check-in: 3a2c0479 user: drh tags: trunk
2018-12-05
23:45
The sqlite3_normalized_sql() interface should not be transforming quoted identifier names into wildcards. Fix this, and at the same time simplify the code substantially. check-in: e8540377 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

   632    632     }
   633    633   
   634    634     /* Delete any foreign keys attached to this table. */
   635    635     sqlite3FkDelete(db, pTable);
   636    636   
   637    637     /* Delete the Table structure itself.
   638    638     */
          639  +#ifdef SQLITE_ENABLE_NORMALIZE
          640  +  if( pTable->pColHash ){
          641  +    sqlite3HashClear(pTable->pColHash);
          642  +    sqlite3_free(pTable->pColHash);
          643  +  }
          644  +#endif
   639    645     sqlite3DeleteColumnNames(db, pTable);
   640    646     sqlite3DbFree(db, pTable->zName);
   641    647     sqlite3DbFree(db, pTable->zColAff);
   642    648     sqlite3SelectDelete(db, pTable->pSelect);
   643    649     sqlite3ExprListDelete(db, pTable->pCheck);
   644    650   #ifndef SQLITE_OMIT_VIRTUALTABLE
   645    651     sqlite3VtabClear(db, pTable);

Changes to src/prepare.c.

   706    706     rc = sqlite3ApiExit(db, rc);
   707    707     assert( (rc&db->errMask)==rc );
   708    708     sqlite3_mutex_leave(db->mutex);
   709    709     return rc;
   710    710   }
   711    711   
   712    712   #ifdef SQLITE_ENABLE_NORMALIZE
          713  +/*
          714  +** Checks if the specified token is a table, column, or function name,
          715  +** based on the databases associated with the statement being prepared.
          716  +** If the function fails, zero is returned and pRc is filled with the
          717  +** error code.
          718  +*/
          719  +static int shouldTreatAsIdentifier(
          720  +  sqlite3 *db,        /* Database handle. */
          721  +  const char *zToken, /* Pointer to start of token to be checked */
          722  +  int nToken,         /* Length of token to be checked */
          723  +  int *pRc            /* Pointer to error code upon failure */
          724  +){
          725  +  int bFound = 0;     /* Non-zero if token is an identifier name. */
          726  +  int i, j;           /* Database and column loop indexes. */
          727  +  Schema *pSchema;    /* Schema for current database. */
          728  +  Hash *pHash;        /* Hash table of tables for current database. */
          729  +  HashElem *e;        /* Hash element for hash table iteration. */
          730  +  Table *pTab;        /* Database table for columns being checked. */
          731  +  char *zId;          /* Zero terminated name of the identifier */
          732  +  char zSpace[65];    /* Static space for the zero-terminated name */
          733  +
          734  +  if( nToken<sizeof(zSpace) ){
          735  +    memcpy(zSpace, zToken, nToken);
          736  +    zSpace[nToken] = 0;
          737  +    zId = zSpace;
          738  +  }else{
          739  +    zId = sqlite3DbStrNDup(db, zToken, nToken);
          740  +    if( zId==0 ){
          741  +      *pRc = SQLITE_NOMEM_BKPT;
          742  +      return 0;
          743  +    }
          744  +  }
          745  +  if( sqlite3IsRowid(zId) ){
          746  +    bFound = 1;
          747  +    goto done1;
          748  +  }
          749  +  if( nToken>0 ){
          750  +    int hash = SQLITE_FUNC_HASH(sqlite3UpperToLower[(u8)zToken[0]], nToken);
          751  +    if( sqlite3FunctionSearch(hash, zId) ){
          752  +      bFound = 1;
          753  +      goto done1;
          754  +    }
          755  +  }
          756  +  assert( db!=0 );
          757  +  sqlite3_mutex_enter(db->mutex);
          758  +  sqlite3BtreeEnterAll(db);
          759  +  for(i=0; i<db->nDb; i++){
          760  +    pHash = &db->aFunc;
          761  +    if( sqlite3HashFind(pHash, zId) ){
          762  +      bFound = 1;
          763  +      break;
          764  +    }
          765  +    pSchema = db->aDb[i].pSchema;
          766  +    if( pSchema==0 ) continue;
          767  +    pHash = &pSchema->tblHash;
          768  +    if( sqlite3HashFind(pHash, zId) ){
          769  +      bFound = 1;
          770  +      break;
          771  +    }
          772  +    for(e=sqliteHashFirst(pHash); e; e=sqliteHashNext(e)){
          773  +      pTab = sqliteHashData(e);
          774  +      if( pTab==0 ) continue;
          775  +      pHash = pTab->pColHash;
          776  +      if( pHash==0 ){
          777  +        pTab->pColHash = pHash = sqlite3_malloc(sizeof(Hash));
          778  +        if( pHash ){
          779  +          sqlite3HashInit(pHash);
          780  +          for(j=0; j<pTab->nCol; j++){
          781  +            Column *pCol = &pTab->aCol[j];
          782  +            sqlite3HashInsert(pHash, pCol->zName, pCol);
          783  +          }
          784  +        }else{
          785  +          *pRc = SQLITE_NOMEM_BKPT;
          786  +          bFound = 0;
          787  +          goto done2;
          788  +        }
          789  +      }
          790  +      if( pHash && sqlite3HashFind(pHash, zId) ){
          791  +        bFound = 1;
          792  +        goto done2;
          793  +      }
          794  +    }
          795  +  }
          796  +done2:
          797  +  sqlite3BtreeLeaveAll(db);
          798  +  sqlite3_mutex_leave(db->mutex);
          799  +done1:
          800  +  if( zId!=zSpace ) sqlite3DbFree(db, zId);
          801  +  return bFound;
          802  +}
   713    803   
   714    804   /*
   715    805   ** Attempt to estimate the final output buffer size needed for the fully
   716    806   ** normalized version of the specified SQL string.  This should take into
   717    807   ** account any potential expansion that could occur (e.g. via IN clauses
   718    808   ** being expanded, etc).  This size returned is the total number of bytes
   719    809   ** including the NUL terminator.
................................................................................
   778    868   ** Compute a normalization of the SQL given by zSql[0..nSql-1].  Return
   779    869   ** the normalization in space obtained from sqlite3DbMalloc().  Or return
   780    870   ** NULL if anything goes wrong or if zSql is NULL.
   781    871   */
   782    872   char *sqlite3Normalize(
   783    873     Vdbe *pVdbe,      /* VM being reprepared */
   784    874     const char *zSql, /* The original SQL string */
   785         -  int nSql          /* Size of the input string in bytes */
          875  +  int nSql,         /* Size of the input string in bytes */
          876  +  u8 prepFlags      /* The flags passed to sqlite3_prepare_v3() */
   786    877   ){
   787    878     sqlite3 *db;           /* Database handle. */
   788    879     char *z;               /* The output string */
   789    880     int nZ;                /* Size of the output string in bytes */
   790    881     int i;                 /* Next character to read from zSql[] */
   791    882     int j;                 /* Next character to fill in on z[] */
   792    883     int tokenType = 0;     /* Type of the next token */
................................................................................
   881    972           if( j>0 && sqlite3IsIdChar(z[j-1]) && sqlite3IsIdChar(zSql[i]) ){
   882    973             z[j++] = ' ';
   883    974           }
   884    975           if( tokenType==TK_ID ){
   885    976             int i2 = i, n2 = n;
   886    977             if( nParen==nParenAtIN ) iStartIN = 0;
   887    978             if( flags&SQLITE_TOKEN_QUOTED ){ i2++; n2-=2; }
          979  +          if( (prepFlags & SQLITE_PREPARE_CHKIDENTS)!=0 ){
          980  +            int rc = SQLITE_OK;
          981  +            if( shouldTreatAsIdentifier(db, zSql+i2, n2, &rc)==0 ){
          982  +              if( rc!=SQLITE_OK ) goto normalizeError;
          983  +              if( sqlite3_keyword_check(zSql+i2, n2)==0 ){
          984  +                z[j++] = '?';
          985  +                break;
          986  +              }
          987  +            }
          988  +          }
   888    989           }
   889    990           copyNormalizedToken(zSql, i, n, flags, z, &j);
   890    991           break;
   891    992         }
   892    993       }
   893    994     }
   894    995     assert( j<nZ && "one" );

Changes to src/sqlite.h.in.

  3637   3637   ** normalize a SQL statement are unspecified and subject to change.
  3638   3638   ** At a minimum, literal values will be replaced with suitable
  3639   3639   ** placeholders.
  3640   3640   ** </dl>
  3641   3641   */
  3642   3642   #define SQLITE_PREPARE_PERSISTENT              0x01
  3643   3643   #define SQLITE_PREPARE_NORMALIZE               0x02
         3644  +#define SQLITE_PREPARE_CHKIDENTS               0x04
  3644   3645   
  3645   3646   /*
  3646   3647   ** CAPI3REF: Compiling An SQL Statement
  3647   3648   ** KEYWORDS: {SQL statement compiler}
  3648   3649   ** METHOD: sqlite3
  3649   3650   ** CONSTRUCTOR: sqlite3_stmt
  3650   3651   **

Changes to src/sqliteInt.h.

  1953   1953   /*
  1954   1954   ** The schema for each SQL table and view is represented in memory
  1955   1955   ** by an instance of the following structure.
  1956   1956   */
  1957   1957   struct Table {
  1958   1958     char *zName;         /* Name of the table or view */
  1959   1959     Column *aCol;        /* Information about each column */
         1960  +#ifdef SQLITE_ENABLE_NORMALIZE
         1961  +  Hash *pColHash;      /* All columns indexed by name */
         1962  +#endif
  1960   1963     Index *pIndex;       /* List of SQL indexes on this table. */
  1961   1964     Select *pSelect;     /* NULL for tables.  Points to definition if a view. */
  1962   1965     FKey *pFKey;         /* Linked list of all foreign keys in this table */
  1963   1966     char *zColAff;       /* String defining the affinity of each column */
  1964   1967     ExprList *pCheck;    /* All CHECK constraints */
  1965   1968                          /*   ... also used as column name list in a VIEW */
  1966   1969     int tnum;            /* Root BTree page for this table */
................................................................................
  4412   4415   int sqlite3VtabBegin(sqlite3 *, VTable *);
  4413   4416   FuncDef *sqlite3VtabOverloadFunction(sqlite3 *,FuncDef*, int nArg, Expr*);
  4414   4417   sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context*);
  4415   4418   int sqlite3VdbeParameterIndex(Vdbe*, const char*, int);
  4416   4419   int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *);
  4417   4420   void sqlite3ParserReset(Parse*);
  4418   4421   #ifdef SQLITE_ENABLE_NORMALIZE
  4419         -char *sqlite3Normalize(Vdbe*, const char*, int);
         4422  +char *sqlite3Normalize(Vdbe*, const char*, int, u8);
  4420   4423   #endif
  4421   4424   int sqlite3Reprepare(Vdbe*);
  4422   4425   void sqlite3ExprListCheckLength(Parse*, ExprList*, const char*);
  4423   4426   CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
  4424   4427   int sqlite3TempInMemory(const sqlite3*);
  4425   4428   const char *sqlite3JournalModename(int);
  4426   4429   #ifndef SQLITE_OMIT_WAL

Changes to src/vdbeapi.c.

  1711   1711   ** Return the normalized SQL associated with a prepared statement.
  1712   1712   */
  1713   1713   const char *sqlite3_normalized_sql(sqlite3_stmt *pStmt){
  1714   1714     Vdbe *p = (Vdbe *)pStmt;
  1715   1715     if( p==0 ) return 0;
  1716   1716     if( p->zNormSql==0 && p->zSql!=0 ){
  1717   1717       sqlite3_mutex_enter(p->db->mutex);
  1718         -    p->zNormSql = sqlite3Normalize(p, p->zSql, sqlite3Strlen30(p->zSql));
         1718  +    p->zNormSql = sqlite3Normalize(p, p->zSql, sqlite3Strlen30(p->zSql), 0);
  1719   1719       sqlite3_mutex_leave(p->db->mutex);
  1720   1720     }
  1721   1721     return p->zNormSql;
  1722   1722   }
  1723   1723   #endif /* SQLITE_ENABLE_NORMALIZE */
  1724   1724   
  1725   1725   #ifdef SQLITE_ENABLE_PREUPDATE_HOOK

Changes to src/vdbeaux.c.

    63     63       p->expmask = 0;
    64     64     }
    65     65     assert( p->zSql==0 );
    66     66     p->zSql = sqlite3DbStrNDup(p->db, z, n);
    67     67   #ifdef SQLITE_ENABLE_NORMALIZE
    68     68     assert( p->zNormSql==0 );
    69     69     if( p->zSql && (prepFlags & SQLITE_PREPARE_NORMALIZE)!=0 ){
    70         -    p->zNormSql = sqlite3Normalize(p, p->zSql, n);
           70  +    p->zNormSql = sqlite3Normalize(p, p->zSql, n, prepFlags);
    71     71       assert( p->zNormSql!=0 || p->db->mallocFailed );
    72     72     }
    73     73   #endif
    74     74   }
    75     75   
    76     76   /*
    77     77   ** Swap all content between two VDBE structures.

Changes to test/normalize.test.

   205    205     {0 {SELECT a FROM t1 WHERE x IN(?,?,?)AND hex8(?);}}
   206    206   
   207    207     430
   208    208     {SELECT "a" FROM t1 WHERE "x" IN ("1","2",'3');}
   209    209     0x2
   210    210     {0 {SELECT"a"FROM t1 WHERE"x"IN("1","2",?);}}
   211    211   
          212  +  431
          213  +  {SELECT "a" FROM t1 WHERE "x" IN ("1","2",'3');}
          214  +  0x6
          215  +  {0 {SELECT"a"FROM t1 WHERE"x"IN(?,?,?);}}
          216  +
   212    217     440
   213    218     {SELECT 'a' FROM t1 WHERE 'x';}
   214    219     0x2
   215    220     {0 {SELECT?FROM t1 WHERE?;}}
   216    221   
   217    222     450
   218    223     {SELECT [a] FROM t1 WHERE [x];}