/ Check-in [0d8e1504]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0d8e150434bbd179696f1ffe71d1e06cb3d43e6468496c7e481fca8486387bad
User & Date: drh 2018-12-07 16:32:11
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: a92c398f user: drh tags: trunk
17:28
Prototype implementation for the VACUUM INTO command. check-in: 036e3320 user: drh tags: vacuum-into
16:32
Fix the sqlite3_normalized_sql() interface so that it renders double-quoted string literals as "?". check-in: 0d8e1504 user: drh tags: trunk
03:01
Fix dbfuzz2.c so that it works with -DSQLITE_OMIT_INIT check-in: 9ad796a8 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/prepare.c.

   878    878               break;
   879    879             }
   880    880           }
   881    881           if( j>0 && sqlite3IsIdChar(z[j-1]) && sqlite3IsIdChar(zSql[i]) ){
   882    882             z[j++] = ' ';
   883    883           }
   884    884           if( tokenType==TK_ID ){
   885         -          int i2 = i, n2 = n;
          885  +          if( zSql[i]=='"'
          886  +           && sqlite3VdbeUsesDoubleQuotedString(db,pVdbe,zSql+i,n)
          887  +          ){
          888  +            z[j++] = '?';
          889  +            break;
          890  +          }
   886    891             if( nParen==nParenAtIN ) iStartIN = 0;
   887         -          if( flags&SQLITE_TOKEN_QUOTED ){ i2++; n2-=2; }
   888    892           }
   889    893           copyNormalizedToken(zSql, i, n, flags, z, &j);
   890    894           break;
   891    895         }
   892    896       }
   893    897     }
   894    898     assert( j<nZ && "one" );

Changes to src/resolve.c.

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

Changes to src/vdbe.h.

   247    247   int sqlite3VdbeReset(Vdbe*);
   248    248   void sqlite3VdbeSetNumCols(Vdbe*,int);
   249    249   int sqlite3VdbeSetColName(Vdbe*, int, int, const char *, void(*)(void*));
   250    250   void sqlite3VdbeCountChanges(Vdbe*);
   251    251   sqlite3 *sqlite3VdbeDb(Vdbe*);
   252    252   u8 sqlite3VdbePrepareFlags(Vdbe*);
   253    253   void sqlite3VdbeSetSql(Vdbe*, const char *z, int n, u8);
          254  +#ifdef SQLITE_ENABLE_NORMALIZE
          255  +void sqlite3VdbeAddDblquoteStr(sqlite3*,Vdbe*,const char*);
          256  +int sqlite3VdbeUsesDoubleQuotedString(sqlite3*,Vdbe*,const char*,int);
          257  +#endif
   254    258   void sqlite3VdbeSwap(Vdbe*,Vdbe*);
   255    259   VdbeOp *sqlite3VdbeTakeOpArray(Vdbe*, int*, int*);
   256    260   sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe*, int, u8);
   257    261   void sqlite3VdbeSetVarmask(Vdbe*, int);
   258    262   #ifndef SQLITE_OMIT_TRACE
   259    263     char *sqlite3VdbeExpandSql(Vdbe*, const char*);
   260    264   #endif

Changes to src/vdbeInt.h.

   331    331   };
   332    332   
   333    333   /* A bitfield type for use inside of structures.  Always follow with :N where
   334    334   ** N is the number of bits.
   335    335   */
   336    336   typedef unsigned bft;  /* Bit Field Type */
   337    337   
          338  +/* The ScanStatus object holds a single value for the
          339  +** sqlite3_stmt_scanstatus() interface.
          340  +*/
   338    341   typedef struct ScanStatus ScanStatus;
   339    342   struct ScanStatus {
   340    343     int addrExplain;                /* OP_Explain for loop */
   341    344     int addrLoop;                   /* Address of "loops" counter */
   342    345     int addrVisit;                  /* Address of "rows visited" counter */
   343    346     int iSelectID;                  /* The "Select-ID" for this loop */
   344    347     LogEst nEst;                    /* Estimated output rows per loop */
   345    348     char *zName;                    /* Name of table or index */
   346    349   };
          350  +
          351  +/* The DblquoteStr object holds the text of a double-quoted
          352  +** string for a prepared statement.  A linked list of these objects
          353  +** is constructed during statement parsing and is held on Vdbe.pDblStr.
          354  +** When computing a normalized SQL statement for an SQL statement, that
          355  +** list is consulted for each double-quoted identifier to see if the
          356  +** identifier should really be a string literal.
          357  +*/
          358  +typedef struct DblquoteStr DblquoteStr;
          359  +struct DblquoteStr {
          360  +  DblquoteStr *pNextStr;   /* Next string literal in the list */
          361  +  char z[8];               /* Dequoted value for the string */
          362  +};
   347    363   
   348    364   /*
   349    365   ** An instance of the virtual machine.  This structure contains the complete
   350    366   ** state of the virtual machine.
   351    367   **
   352    368   ** The "sqlite3_stmt" structure pointer that is returned by sqlite3_prepare()
   353    369   ** is really a pointer to an instance of this structure.
................................................................................
   360    376     u32 magic;              /* Magic number for sanity checking */
   361    377     int nMem;               /* Number of memory locations currently allocated */
   362    378     int nCursor;            /* Number of slots in apCsr[] */
   363    379     u32 cacheCtr;           /* VdbeCursor row cache generation counter */
   364    380     int pc;                 /* The program counter */
   365    381     int rc;                 /* Value to return */
   366    382     int nChange;            /* Number of db changes made since last reset */
   367         -  int iStatement;         /* Statement number (or 0 if has not opened stmt) */
          383  +  int iStatement;         /* Statement number (or 0 if has no opened stmt) */
   368    384     i64 iCurrentTime;       /* Value of julianday('now') for this statement */
   369    385     i64 nFkConstraint;      /* Number of imm. FK constraints this VM */
   370    386     i64 nStmtDefCons;       /* Number of def. constraints when stmt started */
   371    387     i64 nStmtDefImmCons;    /* Number of def. imm constraints when stmt started */
   372    388   
   373    389     /* When allocating a new Vdbe object, all of the fields below should be
   374    390     ** initialized to zero or NULL */
................................................................................
   404    420     bft bIsReader:1;        /* True for statements that read */
   405    421     yDbMask btreeMask;      /* Bitmask of db->aDb[] entries referenced */
   406    422     yDbMask lockMask;       /* Subset of btreeMask that requires a lock */
   407    423     u32 aCounter[7];        /* Counters used by sqlite3_stmt_status() */
   408    424     char *zSql;             /* Text of the SQL statement that generated this */
   409    425   #ifdef SQLITE_ENABLE_NORMALIZE
   410    426     char *zNormSql;         /* Normalization of the associated SQL statement */
          427  +  DblquoteStr *pDblStr;   /* List of double-quoted string literals */
   411    428   #endif
   412    429     void *pFree;            /* Free this when deleting the vdbe */
   413    430     VdbeFrame *pFrame;      /* Parent frame */
   414    431     VdbeFrame *pDelFrame;   /* List of frame objects to free on VM reset */
   415    432     int nFrame;             /* Number of frames in pFrame list */
   416    433     u32 expmask;            /* Binding to these vars invalidates VM */
   417    434     SubProgram *pProgram;   /* Linked list of all sub-programs used by VM */

Changes to src/vdbeaux.c.

    69     69     if( p->zSql && (prepFlags & SQLITE_PREPARE_NORMALIZE)!=0 ){
    70     70       p->zNormSql = sqlite3Normalize(p, p->zSql, n);
    71     71       assert( p->zNormSql!=0 || p->db->mallocFailed );
    72     72     }
    73     73   #endif
    74     74   }
    75     75   
           76  +#ifdef SQLITE_ENABLE_NORMALIZE
           77  +/*
           78  +** Add a new element to the Vdbe->pDblStr list.
           79  +*/
           80  +void sqlite3VdbeAddDblquoteStr(sqlite3 *db, Vdbe *p, const char *z){
           81  +  if( p ){
           82  +    int n = sqlite3Strlen30(z);
           83  +    DblquoteStr *pStr = sqlite3DbMallocRawNN(db,
           84  +                            sizeof(*pStr)+n+1-sizeof(pStr->z));
           85  +    if( pStr ){
           86  +      pStr->pNextStr = p->pDblStr;
           87  +      p->pDblStr = pStr;
           88  +      memcpy(pStr->z, z, n+1);
           89  +    }
           90  +  }
           91  +}
           92  +#endif
           93  +
           94  +#ifdef SQLITE_ENABLE_NORMALIZE
           95  +/*
           96  +** zId of length nId is a double-quoted identifier.  Check to see if
           97  +** that identifier is really used as a string literal.
           98  +*/
           99  +int sqlite3VdbeUsesDoubleQuotedString(
          100  +  sqlite3 *db,            /* Used for transient malloc */
          101  +  Vdbe *pVdbe,            /* The prepared statement */
          102  +  const char *zId,        /* The double-quoted identifier */
          103  +  int nId                 /* Bytes in zId, which is not zero-terminated */
          104  +){
          105  +  char *z;
          106  +  DblquoteStr *pStr;
          107  +  assert( zId!=0 );
          108  +  assert( zId[0]=='"' );
          109  +  assert( nId>=2 );
          110  +  assert( zId[nId-1]=='"' );
          111  +  if( pVdbe->pDblStr==0 ) return 0;
          112  +  z = sqlite3DbStrNDup(db, zId, nId);
          113  +  if( z==0 ) return 0;
          114  +  sqlite3Dequote(z);
          115  +  for(pStr=pVdbe->pDblStr; pStr; pStr=pStr->pNextStr){
          116  +    if( strcmp(z, pStr->z)==0 ) break;
          117  +  }
          118  +  sqlite3DbFree(db, z);
          119  +  return pStr!=0;
          120  +}
          121  +#endif
          122  +
    76    123   /*
    77    124   ** Swap all content between two VDBE structures.
    78    125   */
    79    126   void sqlite3VdbeSwap(Vdbe *pA, Vdbe *pB){
    80    127     Vdbe tmp, *pTmp;
    81    128     char *zTmp;
    82    129     assert( pA->db==pB->db );
................................................................................
    88    135     pB->pNext = pTmp;
    89    136     pTmp = pA->pPrev;
    90    137     pA->pPrev = pB->pPrev;
    91    138     pB->pPrev = pTmp;
    92    139     zTmp = pA->zSql;
    93    140     pA->zSql = pB->zSql;
    94    141     pB->zSql = zTmp;
    95         -#ifdef SQLITE_ENABLE_NORMALIZE
          142  +#if 0
    96    143     zTmp = pA->zNormSql;
    97    144     pA->zNormSql = pB->zNormSql;
    98    145     pB->zNormSql = zTmp;
    99    146   #endif
   100    147     pB->expmask = pA->expmask;
   101    148     pB->prepFlags = pA->prepFlags;
   102    149     memcpy(pB->aCounter, pA->aCounter, sizeof(pB->aCounter));
................................................................................
  3166   3213       sqlite3DbFree(db, p->pFree);
  3167   3214     }
  3168   3215     vdbeFreeOpArray(db, p->aOp, p->nOp);
  3169   3216     sqlite3DbFree(db, p->aColName);
  3170   3217     sqlite3DbFree(db, p->zSql);
  3171   3218   #ifdef SQLITE_ENABLE_NORMALIZE
  3172   3219     sqlite3DbFree(db, p->zNormSql);
         3220  +  {
         3221  +    DblquoteStr *pThis, *pNext;
         3222  +    for(pThis=p->pDblStr; pThis; pThis=pNext){
         3223  +      pNext = pThis->pNextStr;
         3224  +      sqlite3DbFree(db, pThis);
         3225  +    }
         3226  +  }
  3173   3227   #endif
  3174   3228   #ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  3175   3229     {
  3176   3230       int i;
  3177   3231       for(i=0; i<p->nScan; i++){
  3178   3232         sqlite3DbFree(db, p->aScan[i].zName);
  3179   3233       }

Changes to test/normalize.test.

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