Merge pull request #8713 from m-kuhn/sqlite_quoted_value_to_qgssqlite…

…utils Move sqlite quoted value methods to QgsSqliteUtils
qgis · Dec 20, 2018 · bd87b35 · bd87b35
2 parents dab069e + c03f1a6
commit bd87b35
Show file tree

Hide file tree

Showing 7 changed files with 150 additions and 87 deletions.
diff --git a/src/core/qgssqliteexpressioncompiler.cpp b/src/core/qgssqliteexpressioncompiler.cpp
@@ -18,6 +18,7 @@
 #include "qgssqliteexpressioncompiler.h"
 #include "qgssqlexpressioncompiler.h"
 #include "qgsexpressionnodeimpl.h"
+#include "qgssqliteutils.h"
 
 QgsSQLiteExpressionCompiler::QgsSQLiteExpressionCompiler( const QgsFields &fields )
   : QgsSqlExpressionCompiler( fields, QgsSqlExpressionCompiler::LikeIsCaseInsensitive | QgsSqlExpressionCompiler::IntegerDivisionResultsInInteger )
@@ -51,38 +52,13 @@ QgsSqlExpressionCompiler::Result QgsSQLiteExpressionCompiler::compileNode( const
 
 QString QgsSQLiteExpressionCompiler::quotedIdentifier( const QString &identifier )
 {
-  QString id( identifier );
-  id.replace( '\"', QLatin1String( "\"\"" ) );
-  return id.prepend( '\"' ).append( '\"' );
+  return QgsSqliteUtils::quotedIdentifier( identifier );
 }
 
 QString QgsSQLiteExpressionCompiler::quotedValue( const QVariant &value, bool &ok )
 {
   ok = true;
-
-  if ( value.isNull() )
-    return QStringLiteral( "NULL" );
-
-  switch ( value.type() )
-  {
-    case QVariant::Int:
-    case QVariant::LongLong:
-    case QVariant::Double:
-      return value.toString();
-
-    case QVariant::Bool:
-      //SQLite has no boolean literals
-      return value.toBool() ? "1" : "0";
-
-    default:
-    case QVariant::String:
-      QString v = value.toString();
-      // https://www.sqlite.org/lang_expr.html :
-      // """A string constant is formed by enclosing the string in single quotes (').
-      // A single quote within the string can be encoded by putting two single quotes
-      // in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. """
-      return v.replace( '\'', QLatin1String( "''" ) ).prepend( '\'' ).append( '\'' );
-  }
+  return QgsSqliteUtils::quotedValue( value );
 }
 
 QString QgsSQLiteExpressionCompiler::sqlFunctionFromFunctionName( const QString &fnName ) const

diff --git a/src/core/qgssqliteutils.cpp b/src/core/qgssqliteutils.cpp
@@ -19,6 +19,7 @@
 
 #include <sqlite3.h>
 #include <cstdarg>
+#include <QVariant>
 
 void QgsSqlite3Closer::operator()( sqlite3 *database )
 {
@@ -101,6 +102,40 @@ QString QgsSqliteUtils::quotedString( const QString &value )
   return v.prepend( '\'' ).append( '\'' );
 }
 
+QString QgsSqliteUtils::quotedIdentifier( const QString &identifier )
+{
+  QString id( identifier );
+  id.replace( '\"', QLatin1String( "\"\"" ) );
+  return id.prepend( '\"' ).append( '\"' );
+}
+
+QString QgsSqliteUtils::quotedValue( const QVariant &value )
+{
+  if ( value.isNull() )
+    return QStringLiteral( "NULL" );
+
+  switch ( value.type() )
+  {
+    case QVariant::Int:
+    case QVariant::LongLong:
+    case QVariant::Double:
+      return value.toString();
+
+    case QVariant::Bool:
+      //SQLite has no boolean literals
+      return value.toBool() ? QStringLiteral( "1" ) : QStringLiteral( "0" );
+
+    default:
+    case QVariant::String:
+      QString v = value.toString();
+      // https://www.sqlite.org/lang_expr.html :
+      // """A string constant is formed by enclosing the string in single quotes (').
+      // A single quote within the string can be encoded by putting two single quotes
+      // in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. """
+      return v.replace( '\'', QLatin1String( "''" ) ).prepend( '\'' ).append( '\'' );
+  }
+}
+
 QString QgsSqlite3Mprintf( const char *format, ... )
 {
   va_list ap;

diff --git a/src/core/qgssqliteutils.h b/src/core/qgssqliteutils.h
@@ -26,6 +26,7 @@
 
 struct sqlite3;
 struct sqlite3_stmt;
+class QVariant;
 
 /**
  * \ingroup core
@@ -153,6 +154,21 @@ class CORE_EXPORT QgsSqliteUtils
      * characters correctly escaped.
      */
     static QString quotedString( const QString &value );
+
+    /**
+     * Returns a properly quoted version of \a identifier.
+     *
+     * \since QGIS 3.6
+     */
+    static QString quotedIdentifier( const QString &identifier );
+
+    /**
+     * Returns a properly quoted and escaped version of \a value
+     * for use in SQL strings.
+     *
+     * \since QGIS 3.6
+     */
+    static QString quotedValue( const QVariant &value );
 };
 
 /**

diff --git a/src/providers/spatialite/qgsspatialitefeatureiterator.cpp b/src/providers/spatialite/qgsspatialitefeatureiterator.cpp
@@ -341,7 +341,7 @@ bool QgsSpatiaLiteFeatureIterator::prepareStatement( const QString &whereClause,
 
     if ( mFetchGeometry )
     {
-      sql += QStringLiteral( ", AsBinary(%1)" ).arg( QgsSpatiaLiteProvider::quotedIdentifier( mSource->mGeometryColumn ) );
+      sql += QStringLiteral( ", AsBinary(%1)" ).arg( QgsSqliteUtils::quotedIdentifier( mSource->mGeometryColumn ) );
       mGeomColIdx = colIdx;
     }
     sql += QStringLiteral( " FROM %1" ).arg( mSource->mQuery );
@@ -375,7 +375,7 @@ bool QgsSpatiaLiteFeatureIterator::prepareStatement( const QString &whereClause,
 
 QString QgsSpatiaLiteFeatureIterator::quotedPrimaryKey()
 {
-  return mSource->mPrimaryKey.isEmpty() ? QStringLiteral( "ROWID" ) : QgsSpatiaLiteProvider::quotedIdentifier( mSource->mPrimaryKey );
+  return mSource->mPrimaryKey.isEmpty() ? QStringLiteral( "ROWID" ) : QgsSqliteUtils::quotedIdentifier( mSource->mPrimaryKey );
 }
 
 QString QgsSpatiaLiteFeatureIterator::whereClauseFid()
@@ -405,12 +405,12 @@ QString QgsSpatiaLiteFeatureIterator::whereClauseRect()
   if ( mRequest.flags() & QgsFeatureRequest::ExactIntersect )
   {
     // we are requested to evaluate a true INTERSECT relationship
-    whereClause += QStringLiteral( "Intersects(%1, BuildMbr(%2)) AND " ).arg( QgsSpatiaLiteProvider::quotedIdentifier( mSource->mGeometryColumn ), mbr( mFilterRect ) );
+    whereClause += QStringLiteral( "Intersects(%1, BuildMbr(%2)) AND " ).arg( QgsSqliteUtils::quotedIdentifier( mSource->mGeometryColumn ), mbr( mFilterRect ) );
   }
   if ( mSource->mVShapeBased )
   {
     // handling a VirtualShape layer
-    whereClause += QStringLiteral( "MbrIntersects(%1, BuildMbr(%2))" ).arg( QgsSpatiaLiteProvider::quotedIdentifier( mSource->mGeometryColumn ), mbr( mFilterRect ) );
+    whereClause += QStringLiteral( "MbrIntersects(%1, BuildMbr(%2))" ).arg( QgsSqliteUtils::quotedIdentifier( mSource->mGeometryColumn ), mbr( mFilterRect ) );
   }
   else if ( mFilterRect.isFinite() )
   {
@@ -424,7 +424,7 @@ QString QgsSpatiaLiteFeatureIterator::whereClauseRect()
       QString idxName = QStringLiteral( "idx_%1_%2" ).arg( mSource->mIndexTable, mSource->mIndexGeometry );
       whereClause += QStringLiteral( "%1 IN (SELECT pkid FROM %2 WHERE %3)" )
                      .arg( quotedPrimaryKey(),
-                           QgsSpatiaLiteProvider::quotedIdentifier( idxName ),
+                           QgsSqliteUtils::quotedIdentifier( idxName ),
                            mbrFilter );
     }
     else if ( mSource->mSpatialIndexMbrCache )
@@ -433,13 +433,13 @@ QString QgsSpatiaLiteFeatureIterator::whereClauseRect()
       QString idxName = QStringLiteral( "cache_%1_%2" ).arg( mSource->mIndexTable, mSource->mIndexGeometry );
       whereClause += QStringLiteral( "%1 IN (SELECT rowid FROM %2 WHERE mbr = FilterMbrIntersects(%3))" )
                      .arg( quotedPrimaryKey(),
-                           QgsSpatiaLiteProvider::quotedIdentifier( idxName ),
+                           QgsSqliteUtils::quotedIdentifier( idxName ),
                            mbr( mFilterRect ) );
     }
     else
     {
       // using simple MBR filtering
-      whereClause += QStringLiteral( "MbrIntersects(%1, BuildMbr(%2))" ).arg( QgsSpatiaLiteProvider::quotedIdentifier( mSource->mGeometryColumn ), mbr( mFilterRect ) );
+      whereClause += QStringLiteral( "MbrIntersects(%1, BuildMbr(%2))" ).arg( QgsSqliteUtils::quotedIdentifier( mSource->mGeometryColumn ), mbr( mFilterRect ) );
     }
   }
   else
@@ -462,7 +462,7 @@ QString QgsSpatiaLiteFeatureIterator::mbr( const QgsRectangle &rect )
 
 QString QgsSpatiaLiteFeatureIterator::fieldName( const QgsField &fld )
 {
-  QString fieldname = QgsSpatiaLiteProvider::quotedIdentifier( fld.name() );
+  QString fieldname = QgsSqliteUtils::quotedIdentifier( fld.name() );
   const QString type = fld.typeName().toLower();
   if ( type.contains( QLatin1String( "geometry" ) ) || type.contains( QLatin1String( "point" ) ) ||
        type.contains( QLatin1String( "line" ) ) || type.contains( QLatin1String( "polygon" ) ) )