SQLite

Check-in [0d8e150434]
Login

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

Overview
Comment:Fix the sqlite3_normalized_sql() interface so that it renders double-quoted string literals as "?".
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0d8e150434bbd179696f1ffe71d1e06cb3d43e6468496c7e481fca8486387bad
User & Date: drh 2018-12-07 16:32:11.362
Context
2018-12-07
20:26
Remove a line of code from VACUUM that is no longer needed, due to the change that allows ATTACH to run within a transaction. (check-in: a92c398fc5 user: drh tags: trunk)
17:28
Prototype implementation for the VACUUM INTO command. (check-in: 036e3320a4 user: drh tags: vacuum-into)
16:32
Fix the sqlite3_normalized_sql() interface so that it renders double-quoted string literals as "?". (check-in: 0d8e150434 user: drh tags: trunk)
03:01
Fix dbfuzz2.c so that it works with -DSQLITE_OMIT_INIT (check-in: 9ad796a882 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/prepare.c.
878
879
880
881
882
883
884



885


886
887
888
889
890
891
892
893
894
            break;
          }
        }
        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" );







>
>
>
|
>
>

<







878
879
880
881
882
883
884
885
886
887
888
889
890
891

892
893
894
895
896
897
898
            break;
          }
        }
        if( j>0 && sqlite3IsIdChar(z[j-1]) && sqlite3IsIdChar(zSql[i]) ){
          z[j++] = ' ';
        }
        if( tokenType==TK_ID ){
          if( zSql[i]=='"'
           && sqlite3VdbeUsesDoubleQuotedString(db,pVdbe,zSql+i,n)
          ){
            z[j++] = '?';
            break;
          }
          if( nParen==nParenAtIN ) iStartIN = 0;

        }
        copyNormalizedToken(zSql, i, n, flags, z, &j);
        break;
      }
    }
  }
  assert( j<nZ && "one" );
Changes to src/resolve.c.
486
487
488
489
490
491
492



493
494
495
496
497
498
499
      **
      ** Someday, I hope to get rid of this hack. Unfortunately there is
      ** a huge amount of legacy SQL that uses it. So for now, we just
      ** issue a warning.
      */
      sqlite3_log(SQLITE_WARNING,
        "double-quoted string literal: \"%w\"", zCol);



      pExpr->op = TK_STRING;
      pExpr->y.pTab = 0;
      return WRC_Prune;
    }
    if( sqlite3ExprIdToTrueFalse(pExpr) ){
      return WRC_Prune;
    }







>
>
>







486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
      **
      ** Someday, I hope to get rid of this hack. Unfortunately there is
      ** a huge amount of legacy SQL that uses it. So for now, we just
      ** issue a warning.
      */
      sqlite3_log(SQLITE_WARNING,
        "double-quoted string literal: \"%w\"", zCol);
#ifdef SQLITE_ENABLE_NORMALIZE
      sqlite3VdbeAddDblquoteStr(db,pParse->pVdbe, zCol);
#endif
      pExpr->op = TK_STRING;
      pExpr->y.pTab = 0;
      return WRC_Prune;
    }
    if( sqlite3ExprIdToTrueFalse(pExpr) ){
      return WRC_Prune;
    }
Changes to src/vdbe.h.
247
248
249
250
251
252
253




254
255
256
257
258
259
260
int sqlite3VdbeReset(Vdbe*);
void sqlite3VdbeSetNumCols(Vdbe*,int);
int sqlite3VdbeSetColName(Vdbe*, int, int, const char *, void(*)(void*));
void sqlite3VdbeCountChanges(Vdbe*);
sqlite3 *sqlite3VdbeDb(Vdbe*);
u8 sqlite3VdbePrepareFlags(Vdbe*);
void sqlite3VdbeSetSql(Vdbe*, const char *z, int n, u8);




void sqlite3VdbeSwap(Vdbe*,Vdbe*);
VdbeOp *sqlite3VdbeTakeOpArray(Vdbe*, int*, int*);
sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe*, int, u8);
void sqlite3VdbeSetVarmask(Vdbe*, int);
#ifndef SQLITE_OMIT_TRACE
  char *sqlite3VdbeExpandSql(Vdbe*, const char*);
#endif







>
>
>
>







247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
int sqlite3VdbeReset(Vdbe*);
void sqlite3VdbeSetNumCols(Vdbe*,int);
int sqlite3VdbeSetColName(Vdbe*, int, int, const char *, void(*)(void*));
void sqlite3VdbeCountChanges(Vdbe*);
sqlite3 *sqlite3VdbeDb(Vdbe*);
u8 sqlite3VdbePrepareFlags(Vdbe*);
void sqlite3VdbeSetSql(Vdbe*, const char *z, int n, u8);
#ifdef SQLITE_ENABLE_NORMALIZE
void sqlite3VdbeAddDblquoteStr(sqlite3*,Vdbe*,const char*);
int sqlite3VdbeUsesDoubleQuotedString(sqlite3*,Vdbe*,const char*,int);
#endif
void sqlite3VdbeSwap(Vdbe*,Vdbe*);
VdbeOp *sqlite3VdbeTakeOpArray(Vdbe*, int*, int*);
sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe*, int, u8);
void sqlite3VdbeSetVarmask(Vdbe*, int);
#ifndef SQLITE_OMIT_TRACE
  char *sqlite3VdbeExpandSql(Vdbe*, const char*);
#endif
Changes to src/vdbeInt.h.
331
332
333
334
335
336
337



338
339
340
341
342
343
344
345
346













347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
};

/* A bitfield type for use inside of structures.  Always follow with :N where
** N is the number of bits.
*/
typedef unsigned bft;  /* Bit Field Type */




typedef struct ScanStatus ScanStatus;
struct ScanStatus {
  int addrExplain;                /* OP_Explain for loop */
  int addrLoop;                   /* Address of "loops" counter */
  int addrVisit;                  /* Address of "rows visited" counter */
  int iSelectID;                  /* The "Select-ID" for this loop */
  LogEst nEst;                    /* Estimated output rows per loop */
  char *zName;                    /* Name of table or index */
};














/*
** An instance of the virtual machine.  This structure contains the complete
** state of the virtual machine.
**
** The "sqlite3_stmt" structure pointer that is returned by sqlite3_prepare()
** is really a pointer to an instance of this structure.
*/
struct Vdbe {
  sqlite3 *db;            /* The database connection that owns this statement */
  Vdbe *pPrev,*pNext;     /* Linked list of VDBEs with the same Vdbe.db */
  Parse *pParse;          /* Parsing context used to create this Vdbe */
  ynVar nVar;             /* Number of entries in aVar[] */
  u32 magic;              /* Magic number for sanity checking */
  int nMem;               /* Number of memory locations currently allocated */
  int nCursor;            /* Number of slots in apCsr[] */
  u32 cacheCtr;           /* VdbeCursor row cache generation counter */
  int pc;                 /* The program counter */
  int rc;                 /* Value to return */
  int nChange;            /* Number of db changes made since last reset */
  int iStatement;         /* Statement number (or 0 if has not opened stmt) */
  i64 iCurrentTime;       /* Value of julianday('now') for this statement */
  i64 nFkConstraint;      /* Number of imm. FK constraints this VM */
  i64 nStmtDefCons;       /* Number of def. constraints when stmt started */
  i64 nStmtDefImmCons;    /* Number of def. imm constraints when stmt started */

  /* When allocating a new Vdbe object, all of the fields below should be
  ** initialized to zero or NULL */







>
>
>









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




















|







331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
};

/* A bitfield type for use inside of structures.  Always follow with :N where
** N is the number of bits.
*/
typedef unsigned bft;  /* Bit Field Type */

/* The ScanStatus object holds a single value for the
** sqlite3_stmt_scanstatus() interface.
*/
typedef struct ScanStatus ScanStatus;
struct ScanStatus {
  int addrExplain;                /* OP_Explain for loop */
  int addrLoop;                   /* Address of "loops" counter */
  int addrVisit;                  /* Address of "rows visited" counter */
  int iSelectID;                  /* The "Select-ID" for this loop */
  LogEst nEst;                    /* Estimated output rows per loop */
  char *zName;                    /* Name of table or index */
};

/* The DblquoteStr object holds the text of a double-quoted
** string for a prepared statement.  A linked list of these objects
** is constructed during statement parsing and is held on Vdbe.pDblStr.
** When computing a normalized SQL statement for an SQL statement, that
** list is consulted for each double-quoted identifier to see if the
** identifier should really be a string literal.
*/
typedef struct DblquoteStr DblquoteStr;
struct DblquoteStr {
  DblquoteStr *pNextStr;   /* Next string literal in the list */
  char z[8];               /* Dequoted value for the string */
};

/*
** An instance of the virtual machine.  This structure contains the complete
** state of the virtual machine.
**
** The "sqlite3_stmt" structure pointer that is returned by sqlite3_prepare()
** is really a pointer to an instance of this structure.
*/
struct Vdbe {
  sqlite3 *db;            /* The database connection that owns this statement */
  Vdbe *pPrev,*pNext;     /* Linked list of VDBEs with the same Vdbe.db */
  Parse *pParse;          /* Parsing context used to create this Vdbe */
  ynVar nVar;             /* Number of entries in aVar[] */
  u32 magic;              /* Magic number for sanity checking */
  int nMem;               /* Number of memory locations currently allocated */
  int nCursor;            /* Number of slots in apCsr[] */
  u32 cacheCtr;           /* VdbeCursor row cache generation counter */
  int pc;                 /* The program counter */
  int rc;                 /* Value to return */
  int nChange;            /* Number of db changes made since last reset */
  int iStatement;         /* Statement number (or 0 if has no opened stmt) */
  i64 iCurrentTime;       /* Value of julianday('now') for this statement */
  i64 nFkConstraint;      /* Number of imm. FK constraints this VM */
  i64 nStmtDefCons;       /* Number of def. constraints when stmt started */
  i64 nStmtDefImmCons;    /* Number of def. imm constraints when stmt started */

  /* When allocating a new Vdbe object, all of the fields below should be
  ** initialized to zero or NULL */
404
405
406
407
408
409
410

411
412
413
414
415
416
417
  bft bIsReader:1;        /* True for statements that read */
  yDbMask btreeMask;      /* Bitmask of db->aDb[] entries referenced */
  yDbMask lockMask;       /* Subset of btreeMask that requires a lock */
  u32 aCounter[7];        /* Counters used by sqlite3_stmt_status() */
  char *zSql;             /* Text of the SQL statement that generated this */
#ifdef SQLITE_ENABLE_NORMALIZE
  char *zNormSql;         /* Normalization of the associated SQL statement */

#endif
  void *pFree;            /* Free this when deleting the vdbe */
  VdbeFrame *pFrame;      /* Parent frame */
  VdbeFrame *pDelFrame;   /* List of frame objects to free on VM reset */
  int nFrame;             /* Number of frames in pFrame list */
  u32 expmask;            /* Binding to these vars invalidates VM */
  SubProgram *pProgram;   /* Linked list of all sub-programs used by VM */







>







420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
  bft bIsReader:1;        /* True for statements that read */
  yDbMask btreeMask;      /* Bitmask of db->aDb[] entries referenced */
  yDbMask lockMask;       /* Subset of btreeMask that requires a lock */
  u32 aCounter[7];        /* Counters used by sqlite3_stmt_status() */
  char *zSql;             /* Text of the SQL statement that generated this */
#ifdef SQLITE_ENABLE_NORMALIZE
  char *zNormSql;         /* Normalization of the associated SQL statement */
  DblquoteStr *pDblStr;   /* List of double-quoted string literals */
#endif
  void *pFree;            /* Free this when deleting the vdbe */
  VdbeFrame *pFrame;      /* Parent frame */
  VdbeFrame *pDelFrame;   /* List of frame objects to free on VM reset */
  int nFrame;             /* Number of frames in pFrame list */
  u32 expmask;            /* Binding to these vars invalidates VM */
  SubProgram *pProgram;   /* Linked list of all sub-programs used by VM */
Changes to src/vdbeaux.c.
69
70
71
72
73
74
75















































76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
  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.
*/
void sqlite3VdbeSwap(Vdbe *pA, Vdbe *pB){
  Vdbe tmp, *pTmp;
  char *zTmp;
  assert( pA->db==pB->db );
  tmp = *pA;
  *pA = *pB;
  *pB = tmp;
  pTmp = pA->pNext;
  pA->pNext = pB->pNext;
  pB->pNext = pTmp;
  pTmp = pA->pPrev;
  pA->pPrev = pB->pPrev;
  pB->pPrev = pTmp;
  zTmp = pA->zSql;
  pA->zSql = pB->zSql;
  pB->zSql = zTmp;
#ifdef SQLITE_ENABLE_NORMALIZE
  zTmp = pA->zNormSql;
  pA->zNormSql = pB->zNormSql;
  pB->zNormSql = zTmp;
#endif
  pB->expmask = pA->expmask;
  pB->prepFlags = pA->prepFlags;
  memcpy(pB->aCounter, pA->aCounter, sizeof(pB->aCounter));







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



















|







69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
  if( p->zSql && (prepFlags & SQLITE_PREPARE_NORMALIZE)!=0 ){
    p->zNormSql = sqlite3Normalize(p, p->zSql, n);
    assert( p->zNormSql!=0 || p->db->mallocFailed );
  }
#endif
}

#ifdef SQLITE_ENABLE_NORMALIZE
/*
** Add a new element to the Vdbe->pDblStr list.
*/
void sqlite3VdbeAddDblquoteStr(sqlite3 *db, Vdbe *p, const char *z){
  if( p ){
    int n = sqlite3Strlen30(z);
    DblquoteStr *pStr = sqlite3DbMallocRawNN(db,
                            sizeof(*pStr)+n+1-sizeof(pStr->z));
    if( pStr ){
      pStr->pNextStr = p->pDblStr;
      p->pDblStr = pStr;
      memcpy(pStr->z, z, n+1);
    }
  }
}
#endif

#ifdef SQLITE_ENABLE_NORMALIZE
/*
** zId of length nId is a double-quoted identifier.  Check to see if
** that identifier is really used as a string literal.
*/
int sqlite3VdbeUsesDoubleQuotedString(
  sqlite3 *db,            /* Used for transient malloc */
  Vdbe *pVdbe,            /* The prepared statement */
  const char *zId,        /* The double-quoted identifier */
  int nId                 /* Bytes in zId, which is not zero-terminated */
){
  char *z;
  DblquoteStr *pStr;
  assert( zId!=0 );
  assert( zId[0]=='"' );
  assert( nId>=2 );
  assert( zId[nId-1]=='"' );
  if( pVdbe->pDblStr==0 ) return 0;
  z = sqlite3DbStrNDup(db, zId, nId);
  if( z==0 ) return 0;
  sqlite3Dequote(z);
  for(pStr=pVdbe->pDblStr; pStr; pStr=pStr->pNextStr){
    if( strcmp(z, pStr->z)==0 ) break;
  }
  sqlite3DbFree(db, z);
  return pStr!=0;
}
#endif

/*
** Swap all content between two VDBE structures.
*/
void sqlite3VdbeSwap(Vdbe *pA, Vdbe *pB){
  Vdbe tmp, *pTmp;
  char *zTmp;
  assert( pA->db==pB->db );
  tmp = *pA;
  *pA = *pB;
  *pB = tmp;
  pTmp = pA->pNext;
  pA->pNext = pB->pNext;
  pB->pNext = pTmp;
  pTmp = pA->pPrev;
  pA->pPrev = pB->pPrev;
  pB->pPrev = pTmp;
  zTmp = pA->zSql;
  pA->zSql = pB->zSql;
  pB->zSql = zTmp;
#if 0
  zTmp = pA->zNormSql;
  pA->zNormSql = pB->zNormSql;
  pB->zNormSql = zTmp;
#endif
  pB->expmask = pA->expmask;
  pB->prepFlags = pA->prepFlags;
  memcpy(pB->aCounter, pA->aCounter, sizeof(pB->aCounter));
3166
3167
3168
3169
3170
3171
3172







3173
3174
3175
3176
3177
3178
3179
    sqlite3DbFree(db, p->pFree);
  }
  vdbeFreeOpArray(db, p->aOp, p->nOp);
  sqlite3DbFree(db, p->aColName);
  sqlite3DbFree(db, p->zSql);
#ifdef SQLITE_ENABLE_NORMALIZE
  sqlite3DbFree(db, p->zNormSql);







#endif
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  {
    int i;
    for(i=0; i<p->nScan; i++){
      sqlite3DbFree(db, p->aScan[i].zName);
    }







>
>
>
>
>
>
>







3213
3214
3215
3216
3217
3218
3219
3220
3221
3222
3223
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233
    sqlite3DbFree(db, p->pFree);
  }
  vdbeFreeOpArray(db, p->aOp, p->nOp);
  sqlite3DbFree(db, p->aColName);
  sqlite3DbFree(db, p->zSql);
#ifdef SQLITE_ENABLE_NORMALIZE
  sqlite3DbFree(db, p->zNormSql);
  {
    DblquoteStr *pThis, *pNext;
    for(pThis=p->pDblStr; pThis; pThis=pNext){
      pNext = pThis->pNextStr;
      sqlite3DbFree(db, pThis);
    }
  }
#endif
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  {
    int i;
    for(i=0; i<p->nScan; i++){
      sqlite3DbFree(db, p->aScan[i].zName);
    }
Changes to test/normalize.test.
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
  {SELECT a FROM t1 WHERE x IN (1,2,3) AND hex8('abc');}
  0x2
  {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







|







203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
  {SELECT a FROM t1 WHERE x IN (1,2,3) AND hex8('abc');}
  0x2
  {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(?,?,?);}}

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

  450