Skip to content

Commit ed35ad9

Browse files
nyalldawsonnyalldawson
committedOct 7, 2018
Unify sqlite string quoting functions to a single QgsSqliteUtils::quoteString
function, with tests
·
ltr-3_40final-3_4_0
1 parent 7e81226 commit ed35ad9

13 files changed

+144
-143
lines changed
 

‎src/app/qgscustomprojectiondialog.cpp

Lines changed: 11 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -141,7 +141,7 @@ bool QgsCustomProjectionDialog::deleteCrs( const QString &id )
141141
{
142142
sqlite3_database_unique_ptr database;
143143

144-
QString sql = "delete from tbl_srs where srs_id=" + quotedValue( id );
144+
QString sql = "delete from tbl_srs where srs_id=" + QgsSqliteUtils::quotedString( id );
145145
QgsDebugMsg( sql );
146146
//check the db is available
147147
int result = database.open( QgsApplication::qgisUserDatabaseFilePath() );
@@ -191,7 +191,7 @@ void QgsCustomProjectionDialog::insertProjection( const QString &projectionAcro
191191
else
192192
{
193193
// Set up the query to retrieve the projection information needed to populate the PROJECTION list
194-
QString srsSql = "select acronym,name,notes,parameters from tbl_projection where acronym=" + quotedValue( projectionAcronym );
194+
QString srsSql = "select acronym,name,notes,parameters from tbl_projection where acronym=" + QgsSqliteUtils::quotedString( projectionAcronym );
195195

196196
sqlite3_statement_unique_ptr srsPreparedStatement = srsDatabase.prepare( srsSql, srsResult );
197197
if ( srsResult == SQLITE_OK )
@@ -201,10 +201,10 @@ void QgsCustomProjectionDialog::insertProjection( const QString &projectionAcro
201201
QgsDebugMsg( "Trying to insert projection" );
202202
// We have the result from system srs.db. Now insert into user db.
203203
sql = "insert into tbl_projection(acronym,name,notes,parameters) values ("
204-
+ quotedValue( srsPreparedStatement.columnAsText( 0 ) )
205-
+ ',' + quotedValue( srsPreparedStatement.columnAsText( 1 ) )
206-
+ ',' + quotedValue( srsPreparedStatement.columnAsText( 2 ) )
207-
+ ',' + quotedValue( srsPreparedStatement.columnAsText( 3 ) )
204+
+ QgsSqliteUtils::quotedString( srsPreparedStatement.columnAsText( 0 ) )
205+
+ ',' + QgsSqliteUtils::quotedString( srsPreparedStatement.columnAsText( 1 ) )
206+
+ ',' + QgsSqliteUtils::quotedString( srsPreparedStatement.columnAsText( 2 ) )
207+
+ ',' + QgsSqliteUtils::quotedString( srsPreparedStatement.columnAsText( 3 ) )
208208
+ ')';
209209
sqlite3_statement_unique_ptr preparedStatement = database.prepare( sql, result );
210210
if ( result != SQLITE_OK || preparedStatement.step() != SQLITE_DONE )
@@ -239,12 +239,12 @@ bool QgsCustomProjectionDialog::saveCrs( QgsCoordinateReferenceSystem parameters
239239
else
240240
{
241241
sql = "update tbl_srs set description="
242-
+ quotedValue( name )
243-
+ ",projection_acronym=" + quotedValue( projectionAcronym )
244-
+ ",ellipsoid_acronym=" + quotedValue( ellipsoidAcronym )
245-
+ ",parameters=" + quotedValue( parameters.toProj4() )
242+
+ QgsSqliteUtils::quotedString( name )
243+
+ ",projection_acronym=" + QgsSqliteUtils::quotedString( projectionAcronym )
244+
+ ",ellipsoid_acronym=" + QgsSqliteUtils::quotedString( ellipsoidAcronym )
245+
+ ",parameters=" + QgsSqliteUtils::quotedString( parameters.toProj4() )
246246
+ ",is_geo=0" // <--shamelessly hard coded for now
247-
+ " where srs_id=" + quotedValue( id )
247+
+ " where srs_id=" + QgsSqliteUtils::quotedString( id )
248248
;
249249
QgsDebugMsg( sql );
250250
sqlite3_database_unique_ptr database;
@@ -521,12 +521,6 @@ void QgsCustomProjectionDialog::pbnCalculate_clicked()
521521
pj_ctx_free( pContext );
522522
}
523523

524-
QString QgsCustomProjectionDialog::quotedValue( QString value )
525-
{
526-
value.replace( '\'', QLatin1String( "''" ) );
527-
return value.prepend( '\'' ).append( '\'' );
528-
}
529-
530524
void QgsCustomProjectionDialog::showHelp()
531525
{
532526
QgsHelp::openHelp( QStringLiteral( "working_with_projections/working_with_projections.html" ) );

‎src/app/qgscustomprojectiondialog.h

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -50,7 +50,6 @@ class APP_EXPORT QgsCustomProjectionDialog : public QDialog, private Ui::QgsCust
5050

5151
//helper functions
5252
void populateList();
53-
QString quotedValue( QString value );
5453
bool deleteCrs( const QString &id );
5554
bool saveCrs( QgsCoordinateReferenceSystem parameters, const QString &name, const QString &id, bool newEntry );
5655
void insertProjection( const QString &projectionAcronym );

‎src/app/qgsnewspatialitelayerdialog.cpp

Lines changed: 6 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -421,11 +421,11 @@ bool QgsNewSpatialiteLayerDialog::apply()
421421
if ( mGeometryTypeBox->currentIndex() != 0 )
422422
{
423423
QString sqlAddGeom = QStringLiteral( "select AddGeometryColumn(%1,%2,%3,%4,%5)" )
424-
.arg( quotedValue( leLayerName->text() ),
425-
quotedValue( leGeometryColumn->text() ) )
424+
.arg( QgsSqliteUtils::quotedString( leLayerName->text() ),
425+
QgsSqliteUtils::quotedString( leGeometryColumn->text() ) )
426426
.arg( mCrsId.split( ':' ).value( 1, QStringLiteral( "0" ) ).toInt() )
427-
.arg( quotedValue( selectedType() ) )
428-
.arg( quotedValue( selectedZM() ) );
427+
.arg( QgsSqliteUtils::quotedString( selectedType() ) )
428+
.arg( QgsSqliteUtils::quotedString( selectedZM() ) );
429429
QgsDebugMsg( sqlAddGeom );
430430

431431
rc = sqlite3_exec( database.get(), sqlAddGeom.toUtf8(), nullptr, nullptr, &errmsg );
@@ -439,8 +439,8 @@ bool QgsNewSpatialiteLayerDialog::apply()
439439
}
440440

441441
QString sqlCreateIndex = QStringLiteral( "select CreateSpatialIndex(%1,%2)" )
442-
.arg( quotedValue( leLayerName->text() ),
443-
quotedValue( leGeometryColumn->text() ) );
442+
.arg( QgsSqliteUtils::quotedString( leLayerName->text() ),
443+
QgsSqliteUtils::quotedString( leGeometryColumn->text() ) );
444444
QgsDebugMsg( sqlCreateIndex );
445445

446446
rc = sqlite3_exec( database.get(), sqlCreateIndex.toUtf8(), nullptr, nullptr, &errmsg );
@@ -488,12 +488,6 @@ QString QgsNewSpatialiteLayerDialog::quotedIdentifier( QString id )
488488
return id.prepend( '\"' ).append( '\"' );
489489
}
490490

491-
QString QgsNewSpatialiteLayerDialog::quotedValue( QString value )
492-
{
493-
value.replace( '\'', QLatin1String( "''" ) );
494-
return value.prepend( '\'' ).append( '\'' );
495-
}
496-
497491
void QgsNewSpatialiteLayerDialog::showHelp()
498492
{
499493
QgsHelp::openHelp( QStringLiteral( "managing_data_source/create_layers.html#creating-a-new-spatialite-layer" ) );

‎src/app/qgsnewspatialitelayerdialog.h

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -66,7 +66,6 @@ class APP_EXPORT QgsNewSpatialiteLayerDialog: public QDialog, private Ui::QgsNew
6666
void showHelp();
6767

6868
static QString quotedIdentifier( QString id );
69-
static QString quotedValue( QString value );
7069

7170
QPushButton *mOkButton = nullptr;
7271
QString mCrsId;

‎src/core/qgscoordinatereferencesystem.cpp

Lines changed: 24 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -498,7 +498,7 @@ bool QgsCoordinateReferenceSystem::loadFromDatabase( const QString &db, const QS
498498

499499
QString mySql = "select srs_id,description,projection_acronym,"
500500
"ellipsoid_acronym,parameters,srid,auth_name||':'||auth_id,is_geo "
501-
"from tbl_srs where " + expression + '=' + quotedValue( value ) + " order by deprecated";
501+
"from tbl_srs where " + expression + '=' + QgsSqliteUtils::quotedString( value ) + " order by deprecated";
502502
statement = database.prepare( mySql, myResult );
503503
// XXX Need to free memory from the error msg if one is set
504504
if ( myResult == SQLITE_OK && statement.step() == SQLITE_ROW )
@@ -729,7 +729,7 @@ bool QgsCoordinateReferenceSystem::createFromProj4( const QString &proj4String )
729729
* We try to match the proj string to and srsid using the following logic:
730730
* - perform a whole text search on proj4 string (if not null)
731731
*/
732-
myRecord = getRecord( "select * from tbl_srs where parameters=" + quotedValue( myProj4String ) + " order by deprecated" );
732+
myRecord = getRecord( "select * from tbl_srs where parameters=" + QgsSqliteUtils::quotedString( myProj4String ) + " order by deprecated" );
733733
if ( myRecord.empty() )
734734
{
735735
// Ticket #722 - aaronr
@@ -764,7 +764,7 @@ bool QgsCoordinateReferenceSystem::createFromProj4( const QString &proj4String )
764764
myStart2 = myLat2RegExp.indexIn( proj4String, myStart2 );
765765
proj4StringModified.replace( myStart2 + LAT_PREFIX_LEN, myLength2 - LAT_PREFIX_LEN, lat1Str );
766766
QgsDebugMsgLevel( "trying proj4string match with swapped lat_1,lat_2", 4 );
767-
myRecord = getRecord( "select * from tbl_srs where parameters=" + quotedValue( proj4StringModified.trimmed() ) + " order by deprecated" );
767+
myRecord = getRecord( "select * from tbl_srs where parameters=" + QgsSqliteUtils::quotedString( proj4StringModified.trimmed() ) + " order by deprecated" );
768768
}
769769
}
770770

@@ -785,7 +785,7 @@ bool QgsCoordinateReferenceSystem::createFromProj4( const QString &proj4String )
785785
QStringList myParams;
786786
Q_FOREACH ( const QString &param, myProj4String.split( QRegExp( "\\s+(?=\\+)" ), QString::SkipEmptyParts ) )
787787
{
788-
QString arg = QStringLiteral( "' '||parameters||' ' LIKE %1" ).arg( quotedValue( QStringLiteral( "% %1 %" ).arg( param.trimmed() ) ) );
788+
QString arg = QStringLiteral( "' '||parameters||' ' LIKE %1" ).arg( QgsSqliteUtils::quotedString( QStringLiteral( "% %1 %" ).arg( param.trimmed() ) ) );
789789
if ( param.startsWith( QLatin1String( "+datum=" ) ) )
790790
{
791791
datum = arg;
@@ -1234,8 +1234,8 @@ long QgsCoordinateReferenceSystem::findMatchingProj()
12341234
// needed to populate the list
12351235
QString mySql = QString( "select srs_id,parameters from tbl_srs where "
12361236
"projection_acronym=%1 and ellipsoid_acronym=%2 order by deprecated" )
1237-
.arg( quotedValue( d->mProjectionAcronym ),
1238-
quotedValue( d->mEllipsoidAcronym ) );
1237+
.arg( QgsSqliteUtils::quotedString( d->mProjectionAcronym ),
1238+
QgsSqliteUtils::quotedString( d->mEllipsoidAcronym ) );
12391239
// Get the full path name to the sqlite3 spatial reference database.
12401240
QString myDatabaseFileName = QgsApplication::srsDatabaseFilePath();
12411241

@@ -1620,19 +1620,19 @@ long QgsCoordinateReferenceSystem::saveAsUserCrs( const QString &name )
16201620
{
16211621
mySql = "insert into tbl_srs (srs_id,description,projection_acronym,ellipsoid_acronym,parameters,is_geo) values ("
16221622
+ QString::number( USER_CRS_START_ID )
1623-
+ ',' + quotedValue( name )
1624-
+ ',' + quotedValue( projectionAcronym() )
1625-
+ ',' + quotedValue( ellipsoidAcronym() )
1626-
+ ',' + quotedValue( toProj4() )
1623+
+ ',' + QgsSqliteUtils::quotedString( name )
1624+
+ ',' + QgsSqliteUtils::quotedString( projectionAcronym() )
1625+
+ ',' + QgsSqliteUtils::quotedString( ellipsoidAcronym() )
1626+
+ ',' + QgsSqliteUtils::quotedString( toProj4() )
16271627
+ ",0)"; // <-- is_geo shamelessly hard coded for now
16281628
}
16291629
else
16301630
{
16311631
mySql = "insert into tbl_srs (description,projection_acronym,ellipsoid_acronym,parameters,is_geo) values ("
1632-
+ quotedValue( name )
1633-
+ ',' + quotedValue( projectionAcronym() )
1634-
+ ',' + quotedValue( ellipsoidAcronym() )
1635-
+ ',' + quotedValue( toProj4() )
1632+
+ QgsSqliteUtils::quotedString( name )
1633+
+ ',' + QgsSqliteUtils::quotedString( projectionAcronym() )
1634+
+ ',' + QgsSqliteUtils::quotedString( ellipsoidAcronym() )
1635+
+ ',' + QgsSqliteUtils::quotedString( toProj4() )
16361636
+ ",0)"; // <-- is_geo shamelessly hard coded for now
16371637
}
16381638
sqlite3_database_unique_ptr database;
@@ -1707,12 +1707,6 @@ long QgsCoordinateReferenceSystem::getRecordCount()
17071707
return myRecordCount;
17081708
}
17091709

1710-
QString QgsCoordinateReferenceSystem::quotedValue( QString value )
1711-
{
1712-
value.replace( '\'', QLatin1String( "''" ) );
1713-
return value.prepend( '\'' ).append( '\'' );
1714-
}
1715-
17161710
// adapted from gdal/ogr/ogr_srs_dict.cpp
17171711
bool QgsCoordinateReferenceSystem::loadWkts( QHash<int, QString> &wkts, const char *filename )
17181712
{
@@ -1951,8 +1945,8 @@ int QgsCoordinateReferenceSystem::syncDatabase()
19511945
{
19521946
errMsg = nullptr;
19531947
sql = QStringLiteral( "UPDATE tbl_srs SET parameters=%1,description=%2,deprecated=%3 WHERE auth_name='EPSG' AND auth_id=%4" )
1954-
.arg( quotedValue( proj4 ) )
1955-
.arg( quotedValue( name ) )
1948+
.arg( QgsSqliteUtils::quotedString( proj4 ) )
1949+
.arg( QgsSqliteUtils::quotedString( name ) )
19561950
.arg( deprecated ? 1 : 0 )
19571951
.arg( it.key() );
19581952

@@ -1989,10 +1983,10 @@ int QgsCoordinateReferenceSystem::syncDatabase()
19891983
}
19901984

19911985
sql = QStringLiteral( "INSERT INTO tbl_srs(description,projection_acronym,ellipsoid_acronym,parameters,srid,auth_name,auth_id,is_geo,deprecated) VALUES (%1,%2,%3,%4,%5,'EPSG',%5,%6,%7)" )
1992-
.arg( quotedValue( name ),
1993-
quotedValue( projRegExp.cap( 1 ) ),
1994-
quotedValue( ellps ),
1995-
quotedValue( proj4 ) )
1986+
.arg( QgsSqliteUtils::quotedString( name ),
1987+
QgsSqliteUtils::quotedString( projRegExp.cap( 1 ) ),
1988+
QgsSqliteUtils::quotedString( ellps ),
1989+
QgsSqliteUtils::quotedString( proj4 ) )
19961990
.arg( it.key() )
19971991
.arg( OSRIsGeographic( crs ) )
19981992
.arg( deprecated ? 1 : 0 );
@@ -2081,9 +2075,9 @@ int QgsCoordinateReferenceSystem::syncDatabase()
20812075
if ( proj4 != params )
20822076
{
20832077
sql = QStringLiteral( "UPDATE tbl_srs SET parameters=%1 WHERE auth_name=%2 AND auth_id=%3" )
2084-
.arg( quotedValue( proj4 ),
2085-
quotedValue( auth_name ),
2086-
quotedValue( auth_id ) );
2078+
.arg( QgsSqliteUtils::quotedString( proj4 ),
2079+
QgsSqliteUtils::quotedString( auth_name ),
2080+
QgsSqliteUtils::quotedString( auth_id ) );
20872081

20882082
if ( sqlite3_exec( database.get(), sql.toUtf8(), nullptr, nullptr, &errMsg ) == SQLITE_OK )
20892083
{
@@ -2291,7 +2285,7 @@ bool QgsCoordinateReferenceSystem::syncDatumTransform( const QString &dbPath )
22912285
int idx = map[i].idx;
22922286
Q_ASSERT( idx != -1 );
22932287
Q_ASSERT( idx < n );
2294-
v.insert( i, *values[ idx ] ? quotedValue( values[idx] ) : QStringLiteral( "NULL" ) );
2288+
v.insert( i, *values[ idx ] ? QgsSqliteUtils::quotedString( values[idx] ) : QStringLiteral( "NULL" ) );
22952289
}
22962290
CSLDestroy( values );
22972291

‎src/core/qgscoordinatereferencesystem.h

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -741,9 +741,6 @@ class CORE_EXPORT QgsCoordinateReferenceSystem
741741
//! Helper for getting number of user CRS already in db
742742
long getRecordCount();
743743

744-
//! Helper for sql-safe value quoting
745-
static QString quotedValue( QString value );
746-
747744
/**
748745
* Initialize the CRS object by looking up CRS database in path given in db argument,
749746
* using first CRS entry where expression = 'value'

‎src/core/qgssqliteutils.cpp

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,16 @@ sqlite3_statement_unique_ptr sqlite3_database_unique_ptr::prepare( const QString
9191
return s;
9292
}
9393

94+
QString QgsSqliteUtils::quotedString( const QString &value )
95+
{
96+
if ( value.isNull() )
97+
return QStringLiteral( "NULL" );
98+
99+
QString v = value;
100+
v.replace( '\'', QLatin1String( "''" ) );
101+
return v.prepend( '\'' ).append( '\'' );
102+
}
103+
94104
QString QgsSqlite3Mprintf( const char *format, ... )
95105
{
96106
va_list ap;

‎src/core/qgssqliteutils.h

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -135,8 +135,24 @@ class CORE_EXPORT sqlite3_database_unique_ptr : public std::unique_ptr< sqlite3,
135135
* argument will be filled with the sqlite3 result code.
136136
*/
137137
sqlite3_statement_unique_ptr prepare( const QString &sql, int &resultCode ) const;
138+
138139
};
139140

141+
/**
142+
* Contains utilities for working with Sqlite data sources.
143+
* \note not available in Python bindings
144+
* \since QGIS 3.4
145+
*/
146+
class CORE_EXPORT QgsSqliteUtils
147+
{
148+
public:
149+
150+
/**
151+
* Returns a quoted string \a value, surround by ' characters and with special
152+
* characters correctly escaped.
153+
*/
154+
static QString quotedString( const QString &value );
155+
};
140156

141157
/**
142158
* Wraps sqlite3_mprintf() by automatically freeing the memory.

‎src/providers/spatialite/qgsspatialiteconnection.cpp

Lines changed: 2 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -454,15 +454,6 @@ bool QgsSpatiaLiteConnection::getTableInfo( sqlite3 *handle, bool loadGeometryle
454454
return false;
455455
}
456456

457-
QString QgsSpatiaLiteConnection::quotedValue( QString value ) const
458-
{
459-
if ( value.isNull() )
460-
return QStringLiteral( "NULL" );
461-
462-
value.replace( '\'', QLatin1String( "''" ) );
463-
return value.prepend( '\'' ).append( '\'' );
464-
}
465-
466457
bool QgsSpatiaLiteConnection::checkGeometryColumnsAuth( sqlite3 *handle )
467458
{
468459
int ret;
@@ -624,8 +615,8 @@ bool QgsSpatiaLiteConnection::isDeclaredHidden( sqlite3 *handle, const QString &
624615
return false;
625616
// checking if some Layer has been declared as HIDDEN
626617
QString sql = QString( "SELECT hidden FROM geometry_columns_auth"
627-
" WHERE f_table_name=%1 and f_geometry_column=%2" ).arg( quotedValue( table ),
628-
quotedValue( geom ) );
618+
" WHERE f_table_name=%1 and f_geometry_column=%2" ).arg( QgsSqliteUtils::quotedString( table ),
619+
QgsSqliteUtils::quotedString( geom ) );
629620

630621
ret = sqlite3_get_table( handle, sql.toUtf8().constData(), &results, &rows, &columns, &errMsg );
631622
if ( ret != SQLITE_OK )

‎src/providers/spatialite/qgsspatialiteconnection.h

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -104,9 +104,6 @@ class QgsSpatiaLiteConnection : public QObject
104104
*/
105105
bool getTableInfoAbstractInterface( sqlite3 *handle, bool loadGeometrylessTables );
106106

107-
//! Cleaning well-formatted SQL strings
108-
QString quotedValue( QString value ) const;
109-
110107
//! Checks if geometry_columns_auth table exists
111108
bool checkGeometryColumnsAuth( sqlite3 *handle );
112109

‎src/providers/spatialite/qgsspatialiteprovider.cpp

Lines changed: 55 additions & 64 deletions
Large diffs are not rendered by default.

‎src/providers/spatialite/qgsspatialiteprovider.h

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -144,7 +144,6 @@ class QgsSpatiaLiteProvider: public QgsVectorDataProvider
144144
static int computeMultiWKB3Dsize( const unsigned char *p_in, int little_endian,
145145
int endian_arch );
146146
static QString quotedIdentifier( QString id );
147-
static QString quotedValue( QString value );
148147

149148
struct SLFieldNotFound {}; //! Exception to throw
150149

‎tests/src/core/testqgssqliteutils.cpp

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,8 @@ class TestQgsSqliteUtils : public QObject
4646

4747
void testPrintfAscii();
4848
void testPrintfUtf8();
49+
void testQuotedString_data();
50+
void testQuotedString();
4951
};
5052

5153

@@ -90,6 +92,24 @@ void TestQgsSqliteUtils::testPrintfUtf8()
9092
QCOMPARE( query, QString( "SELECT id FROM tag WHERE LOWER(name)='%1'" ).arg( lowerTag ) );
9193
}
9294

95+
void TestQgsSqliteUtils::testQuotedString_data()
96+
{
97+
QTest::addColumn<QString>( "input" );
98+
QTest::addColumn<QString>( "expected" );
99+
100+
QTest::newRow( "test 1" ) << "university of qgis" << "'university of qgis'";
101+
QTest::newRow( "test 2" ) << "university of 'qgis'" << "'university of ''qgis'''";
102+
QTest::newRow( "test NULL" ) << QString() << "NULL";
103+
}
104+
105+
void TestQgsSqliteUtils::testQuotedString()
106+
{
107+
QFETCH( QString, input );
108+
QFETCH( QString, expected );
109+
110+
QCOMPARE( QgsSqliteUtils::quotedString( input ), expected );
111+
}
112+
93113

94114
QGSTEST_MAIN( TestQgsSqliteUtils )
95115
#include "testqgssqliteutils.moc"

0 commit comments

Comments
 (0)
Please sign in to comment.