/ 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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

632
633
634
635
636
637
638






639
640
641
642
643
644
645
  }

  /* Delete any foreign keys attached to this table. */
  sqlite3FkDelete(db, pTable);

  /* Delete the Table structure itself.
  */






  sqlite3DeleteColumnNames(db, pTable);
  sqlite3DbFree(db, pTable->zName);
  sqlite3DbFree(db, pTable->zColAff);
  sqlite3SelectDelete(db, pTable->pSelect);
  sqlite3ExprListDelete(db, pTable->pCheck);
#ifndef SQLITE_OMIT_VIRTUALTABLE
  sqlite3VtabClear(db, pTable);







>
>
>
>
>
>







632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
  }

  /* Delete any foreign keys attached to this table. */
  sqlite3FkDelete(db, pTable);

  /* Delete the Table structure itself.
  */
#ifdef SQLITE_ENABLE_NORMALIZE
  if( pTable->pColHash ){
    sqlite3HashClear(pTable->pColHash);
    sqlite3_free(pTable->pColHash);
  }
#endif
  sqlite3DeleteColumnNames(db, pTable);
  sqlite3DbFree(db, pTable->zName);
  sqlite3DbFree(db, pTable->zColAff);
  sqlite3SelectDelete(db, pTable->pSelect);
  sqlite3ExprListDelete(db, pTable->pCheck);
#ifndef SQLITE_OMIT_VIRTUALTABLE
  sqlite3VtabClear(db, pTable);

Changes to src/prepare.c.

706
707
708
709
710
711
712


























































































713
714
715
716
717
718
719
...
778
779
780
781
782
783
784
785

786
787
788
789
790
791
792
...
881
882
883
884
885
886
887










888
889
890
891
892
893
894
  rc = sqlite3ApiExit(db, rc);
  assert( (rc&db->errMask)==rc );
  sqlite3_mutex_leave(db->mutex);
  return rc;
}

#ifdef SQLITE_ENABLE_NORMALIZE



























































































/*
** Attempt to estimate the final output buffer size needed for the fully
** normalized version of the specified SQL string.  This should take into
** account any potential expansion that could occur (e.g. via IN clauses
** being expanded, etc).  This size returned is the total number of bytes
** including the NUL terminator.
................................................................................
** Compute a normalization of the SQL given by zSql[0..nSql-1].  Return
** the normalization in space obtained from sqlite3DbMalloc().  Or return
** NULL if anything goes wrong or if zSql is NULL.
*/
char *sqlite3Normalize(
  Vdbe *pVdbe,      /* VM being reprepared */
  const char *zSql, /* The original SQL string */
  int nSql          /* Size of the input string in bytes */

){
  sqlite3 *db;           /* Database handle. */
  char *z;               /* The output string */
  int nZ;                /* Size of the output string in bytes */
  int i;                 /* Next character to read from zSql[] */
  int j;                 /* Next character to fill in on z[] */
  int tokenType = 0;     /* Type of the next token */
................................................................................
        if( j>0 && sqlite3IsIdChar(z[j-1]) && sqlite3IsIdChar(zSql[i]) ){
          z[j++] = ' ';
        }
        if( tokenType==TK_ID ){
          int i2 = i, n2 = n;
          if( nParen==nParenAtIN ) iStartIN = 0;
          if( flags&SQLITE_TOKEN_QUOTED ){ i2++; n2-=2; }










        }
        copyNormalizedToken(zSql, i, n, flags, z, &j);
        break;
      }
    }
  }
  assert( j<nZ && "one" );







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|
>







 







>
>
>
>
>
>
>
>
>
>







706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
...
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
...
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
  rc = sqlite3ApiExit(db, rc);
  assert( (rc&db->errMask)==rc );
  sqlite3_mutex_leave(db->mutex);
  return rc;
}

#ifdef SQLITE_ENABLE_NORMALIZE
/*
** Checks if the specified token is a table, column, or function name,
** based on the databases associated with the statement being prepared.
** If the function fails, zero is returned and pRc is filled with the
** error code.
*/
static int shouldTreatAsIdentifier(
  sqlite3 *db,        /* Database handle. */
  const char *zToken, /* Pointer to start of token to be checked */
  int nToken,         /* Length of token to be checked */
  int *pRc            /* Pointer to error code upon failure */
){
  int bFound = 0;     /* Non-zero if token is an identifier name. */
  int i, j;           /* Database and column loop indexes. */
  Schema *pSchema;    /* Schema for current database. */
  Hash *pHash;        /* Hash table of tables for current database. */
  HashElem *e;        /* Hash element for hash table iteration. */
  Table *pTab;        /* Database table for columns being checked. */
  char *zId;          /* Zero terminated name of the identifier */
  char zSpace[65];    /* Static space for the zero-terminated name */

  if( nToken<sizeof(zSpace) ){
    memcpy(zSpace, zToken, nToken);
    zSpace[nToken] = 0;
    zId = zSpace;
  }else{
    zId = sqlite3DbStrNDup(db, zToken, nToken);
    if( zId==0 ){
      *pRc = SQLITE_NOMEM_BKPT;
      return 0;
    }
  }
  if( sqlite3IsRowid(zId) ){
    bFound = 1;
    goto done1;
  }
  if( nToken>0 ){
    int hash = SQLITE_FUNC_HASH(sqlite3UpperToLower[(u8)zToken[0]], nToken);
    if( sqlite3FunctionSearch(hash, zId) ){
      bFound = 1;
      goto done1;
    }
  }
  assert( db!=0 );
  sqlite3_mutex_enter(db->mutex);
  sqlite3BtreeEnterAll(db);
  for(i=0; i<db->nDb; i++){
    pHash = &db->aFunc;
    if( sqlite3HashFind(pHash, zId) ){
      bFound = 1;
      break;
    }
    pSchema = db->aDb[i].pSchema;
    if( pSchema==0 ) continue;
    pHash = &pSchema->tblHash;
    if( sqlite3HashFind(pHash, zId) ){
      bFound = 1;
      break;
    }
    for(e=sqliteHashFirst(pHash); e; e=sqliteHashNext(e)){
      pTab = sqliteHashData(e);
      if( pTab==0 ) continue;
      pHash = pTab->pColHash;
      if( pHash==0 ){
        pTab->pColHash = pHash = sqlite3_malloc(sizeof(Hash));
        if( pHash ){
          sqlite3HashInit(pHash);
          for(j=0; j<pTab->nCol; j++){
            Column *pCol = &pTab->aCol[j];
            sqlite3HashInsert(pHash, pCol->zName, pCol);
          }
        }else{
          *pRc = SQLITE_NOMEM_BKPT;
          bFound = 0;
          goto done2;
        }
      }
      if( pHash && sqlite3HashFind(pHash, zId) ){
        bFound = 1;
        goto done2;
      }
    }
  }
done2:
  sqlite3BtreeLeaveAll(db);
  sqlite3_mutex_leave(db->mutex);
done1:
  if( zId!=zSpace ) sqlite3DbFree(db, zId);
  return bFound;
}

/*
** Attempt to estimate the final output buffer size needed for the fully
** normalized version of the specified SQL string.  This should take into
** account any potential expansion that could occur (e.g. via IN clauses
** being expanded, etc).  This size returned is the total number of bytes
** including the NUL terminator.
................................................................................
** Compute a normalization of the SQL given by zSql[0..nSql-1].  Return
** the normalization in space obtained from sqlite3DbMalloc().  Or return
** NULL if anything goes wrong or if zSql is NULL.
*/
char *sqlite3Normalize(
  Vdbe *pVdbe,      /* VM being reprepared */
  const char *zSql, /* The original SQL string */
  int nSql,         /* Size of the input string in bytes */
  u8 prepFlags      /* The flags passed to sqlite3_prepare_v3() */
){
  sqlite3 *db;           /* Database handle. */
  char *z;               /* The output string */
  int nZ;                /* Size of the output string in bytes */
  int i;                 /* Next character to read from zSql[] */
  int j;                 /* Next character to fill in on z[] */
  int tokenType = 0;     /* Type of the next token */
................................................................................
        if( j>0 && sqlite3IsIdChar(z[j-1]) && sqlite3IsIdChar(zSql[i]) ){
          z[j++] = ' ';
        }
        if( tokenType==TK_ID ){
          int i2 = i, n2 = n;
          if( nParen==nParenAtIN ) iStartIN = 0;
          if( flags&SQLITE_TOKEN_QUOTED ){ i2++; n2-=2; }
          if( (prepFlags & SQLITE_PREPARE_CHKIDENTS)!=0 ){
            int rc = SQLITE_OK;
            if( shouldTreatAsIdentifier(db, zSql+i2, n2, &rc)==0 ){
              if( rc!=SQLITE_OK ) goto normalizeError;
              if( sqlite3_keyword_check(zSql+i2, n2)==0 ){
                z[j++] = '?';
                break;
              }
            }
          }
        }
        copyNormalizedToken(zSql, i, n, flags, z, &j);
        break;
      }
    }
  }
  assert( j<nZ && "one" );

Changes to src/sqlite.h.in.

3637
3638
3639
3640
3641
3642
3643

3644
3645
3646
3647
3648
3649
3650
** normalize a SQL statement are unspecified and subject to change.
** At a minimum, literal values will be replaced with suitable
** placeholders.
** </dl>
*/
#define SQLITE_PREPARE_PERSISTENT              0x01
#define SQLITE_PREPARE_NORMALIZE               0x02


/*
** CAPI3REF: Compiling An SQL Statement
** KEYWORDS: {SQL statement compiler}
** METHOD: sqlite3
** CONSTRUCTOR: sqlite3_stmt
**







>







3637
3638
3639
3640
3641
3642
3643
3644
3645
3646
3647
3648
3649
3650
3651
** normalize a SQL statement are unspecified and subject to change.
** At a minimum, literal values will be replaced with suitable
** placeholders.
** </dl>
*/
#define SQLITE_PREPARE_PERSISTENT              0x01
#define SQLITE_PREPARE_NORMALIZE               0x02
#define SQLITE_PREPARE_CHKIDENTS               0x04

/*
** CAPI3REF: Compiling An SQL Statement
** KEYWORDS: {SQL statement compiler}
** METHOD: sqlite3
** CONSTRUCTOR: sqlite3_stmt
**

Changes to src/sqliteInt.h.

1953
1954
1955
1956
1957
1958
1959



1960
1961
1962
1963
1964
1965
1966
....
4412
4413
4414
4415
4416
4417
4418
4419
4420
4421
4422
4423
4424
4425
4426
/*
** The schema for each SQL table and view is represented in memory
** by an instance of the following structure.
*/
struct Table {
  char *zName;         /* Name of the table or view */
  Column *aCol;        /* Information about each column */



  Index *pIndex;       /* List of SQL indexes on this table. */
  Select *pSelect;     /* NULL for tables.  Points to definition if a view. */
  FKey *pFKey;         /* Linked list of all foreign keys in this table */
  char *zColAff;       /* String defining the affinity of each column */
  ExprList *pCheck;    /* All CHECK constraints */
                       /*   ... also used as column name list in a VIEW */
  int tnum;            /* Root BTree page for this table */
................................................................................
int sqlite3VtabBegin(sqlite3 *, VTable *);
FuncDef *sqlite3VtabOverloadFunction(sqlite3 *,FuncDef*, int nArg, Expr*);
sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context*);
int sqlite3VdbeParameterIndex(Vdbe*, const char*, int);
int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *);
void sqlite3ParserReset(Parse*);
#ifdef SQLITE_ENABLE_NORMALIZE
char *sqlite3Normalize(Vdbe*, const char*, int);
#endif
int sqlite3Reprepare(Vdbe*);
void sqlite3ExprListCheckLength(Parse*, ExprList*, const char*);
CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
int sqlite3TempInMemory(const sqlite3*);
const char *sqlite3JournalModename(int);
#ifndef SQLITE_OMIT_WAL







>
>
>







 







|







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

Changes to src/vdbeapi.c.

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

#ifdef SQLITE_ENABLE_PREUPDATE_HOOK







|







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

#ifdef SQLITE_ENABLE_PREUPDATE_HOOK

Changes to src/vdbeaux.c.

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

/*
** Swap all content between two VDBE structures.







|







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

/*
** Swap all content between two VDBE structures.

Changes to test/normalize.test.

205
206
207
208
209
210
211





212
213
214
215
216
217
218
  {0 {SELECT a FROM t1 WHERE x IN(?,?,?)AND hex8(?);}}

  430
  {SELECT "a" FROM t1 WHERE "x" IN ("1","2",'3');}
  0x2
  {0 {SELECT"a"FROM t1 WHERE"x"IN("1","2",?);}}






  440
  {SELECT 'a' FROM t1 WHERE 'x';}
  0x2
  {0 {SELECT?FROM t1 WHERE?;}}

  450
  {SELECT [a] FROM t1 WHERE [x];}







>
>
>
>
>







205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
  {0 {SELECT a FROM t1 WHERE x IN(?,?,?)AND hex8(?);}}

  430
  {SELECT "a" FROM t1 WHERE "x" IN ("1","2",'3');}
  0x2
  {0 {SELECT"a"FROM t1 WHERE"x"IN("1","2",?);}}

  431
  {SELECT "a" FROM t1 WHERE "x" IN ("1","2",'3');}
  0x6
  {0 {SELECT"a"FROM t1 WHERE"x"IN(?,?,?);}}

  440
  {SELECT 'a' FROM t1 WHERE 'x';}
  0x2
  {0 {SELECT?FROM t1 WHERE?;}}

  450
  {SELECT [a] FROM t1 WHERE [x];}