Skip to content

Commit 631979c

Browse files
MKleusbergMKleusberg
committed
Improve escpaing support
When generating SQL statements properly escape all identifiers, even those containing backticks which apparently are allowed inside identifiers in SQLite. See issue sqlitebrowser#387.
1 parent 160bc87 commit 631979c

13 files changed

+95
-62
lines changed

‎src/ColumnDisplayFormatDialog.cpp‎

Lines changed: 10 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,6 @@
11
#include "ColumnDisplayFormatDialog.h"
22
#include "ui_ColumnDisplayFormatDialog.h"
3+
#include "sqlitetypes.h"
34

45
ColumnDisplayFormatDialog::ColumnDisplayFormatDialog(const QString& colname, QString current_format, QWidget* parent)
56
: QDialog(parent),
@@ -56,21 +57,21 @@ void ColumnDisplayFormatDialog::updateSqlCode()
5657
QString format = ui->comboDisplayFormat->itemData(ui->comboDisplayFormat->currentIndex()).toString();
5758
#endif
5859
if(format == "default")
59-
ui->editDisplayFormat->setText("`" + column_name + "`");
60+
ui->editDisplayFormat->setText(sqlb::escapeIdentifier(column_name));
6061
else if(format == "lower")
61-
ui->editDisplayFormat->setText("lower(`" + column_name + "`)");
62+
ui->editDisplayFormat->setText("lower(" + sqlb::escapeIdentifier(column_name) + ")");
6263
else if(format == "upper")
63-
ui->editDisplayFormat->setText("upper(`" + column_name + "`)");
64+
ui->editDisplayFormat->setText("upper(" + sqlb::escapeIdentifier(column_name) + ")");
6465
else if(format == "epoch")
65-
ui->editDisplayFormat->setText("datetime(`" + column_name + "`, 'unixepoch')");
66+
ui->editDisplayFormat->setText("datetime(" + sqlb::escapeIdentifier(column_name) + ", 'unixepoch')");
6667
else if(format == "julian")
67-
ui->editDisplayFormat->setText("datetime(`" + column_name + "`)");
68+
ui->editDisplayFormat->setText("datetime(" + sqlb::escapeIdentifier(column_name) + ")");
6869
else if(format == "round")
69-
ui->editDisplayFormat->setText("round(`" + column_name + "`)");
70+
ui->editDisplayFormat->setText("round(" + sqlb::escapeIdentifier(column_name) + ")");
7071
else if(format == "hex")
71-
ui->editDisplayFormat->setText("printf('%x', `" + column_name + "`)");
72+
ui->editDisplayFormat->setText("printf('%x', " + sqlb::escapeIdentifier(column_name) + ")");
7273
else if(format == "octal")
73-
ui->editDisplayFormat->setText("printf('%o', `" + column_name + "`)");
74+
ui->editDisplayFormat->setText("printf('%o', " + sqlb::escapeIdentifier(column_name) + ")");
7475
else if(format == "exponent")
75-
ui->editDisplayFormat->setText("printf('%e', `" + column_name + "`)");
76+
ui->editDisplayFormat->setText("printf('%e', " + sqlb::escapeIdentifier(column_name) + ")");
7677
}

‎src/CreateIndexDialog.cpp‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -57,7 +57,7 @@ void CreateIndexDialog::tableChanged(const QString& new_table)
5757
void CreateIndexDialog::checkInput()
5858
{
5959
bool valid = true;
60-
if(ui->editIndexName->text().isEmpty() || ui->editIndexName->text().contains("`"))
60+
if(ui->editIndexName->text().isEmpty())
6161
valid = false;
6262

6363
int num_columns = 0;
@@ -74,17 +74,17 @@ void CreateIndexDialog::checkInput()
7474

7575
void CreateIndexDialog::accept()
7676
{
77-
QString sql = QString("CREATE %1 INDEX `%2` ON `%3` (")
77+
QString sql = QString("CREATE %1 INDEX %2 ON %3 (")
7878
.arg(ui->checkIndexUnique->isChecked() ? "UNIQUE" : "")
79-
.arg(ui->editIndexName->text())
80-
.arg(ui->comboTableName->currentText());
79+
.arg(sqlb::escapeIdentifier(ui->editIndexName->text()))
80+
.arg(sqlb::escapeIdentifier(ui->comboTableName->currentText()));
8181

8282
for(int i=0; i < ui->tableIndexColumns->rowCount(); ++i)
8383
{
8484
if(ui->tableIndexColumns->item(i, 1)->data(Qt::CheckStateRole) == Qt::Checked)
8585
{
86-
sql.append(QString("`%1` %2,")
87-
.arg(ui->tableIndexColumns->item(i, 0)->text())
86+
sql.append(QString("%1 %2,")
87+
.arg(sqlb::escapeIdentifier(ui->tableIndexColumns->item(i, 0)->text()))
8888
.arg(qobject_cast<QComboBox*>(ui->tableIndexColumns->cellWidget(i, 2))->currentText()));
8989
}
9090
}

‎src/DbStructureModel.cpp‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -218,7 +218,7 @@ QMimeData* DbStructureModel::mimeData(const QModelIndexList& indices) const
218218
tableModel.setTable(data(index.sibling(index.row(), 0), Qt::DisplayRole).toString());
219219
for(int i=0; i < tableModel.rowCount(); ++i)
220220
{
221-
QString insertStatement = "INSERT INTO `" + data(index.sibling(index.row(), 0), Qt::DisplayRole).toString() + "` VALUES(";
221+
QString insertStatement = "INSERT INTO " + sqlb::escapeIdentifier(data(index.sibling(index.row(), 0), Qt::DisplayRole).toString()) + " VALUES(";
222222
for(int j=1; j < tableModel.columnCount(); ++j)
223223
insertStatement += QString("'%1',").arg(tableModel.data(tableModel.index(i, j)).toString());
224224
insertStatement.chop(1);

‎src/EditTableDialog.cpp‎

Lines changed: 13 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@ EditTableDialog::EditTableDialog(DBBrowserDB* db, const QString& tableName, bool
1616
curTable(tableName),
1717
m_table(tableName),
1818
m_bNewTable(createTable),
19-
m_sRestorePointName(QString("edittable_%1_save_%2").arg(curTable).arg(QDateTime::currentMSecsSinceEpoch()))
19+
m_sRestorePointName(sqlb::escapeIdentifier(QString("edittable_%1_save_%2").arg(curTable).arg(QDateTime::currentMSecsSinceEpoch())))
2020
{
2121
// Create UI
2222
ui->setupUi(this);
@@ -173,7 +173,7 @@ void EditTableDialog::checkInput()
173173
{
174174
QString normTableName = ui->editTableName->text().trimmed();
175175
bool valid = true;
176-
if(normTableName.isEmpty() || normTableName.contains("`"))
176+
if(normTableName.isEmpty())
177177
valid = false;
178178
if(ui->treeWidget->topLevelItemCount() == 0)
179179
valid = false;
@@ -272,7 +272,10 @@ void EditTableDialog::itemChanged(QTreeWidgetItem *item, int column)
272272
// we need to check for this case and cancel here. Maybe we can think of some way to modify the INSERT INTO ... SELECT statement
273273
// to at least replace all troublesome NULL values by the default value
274274
SqliteTableModel m(this, pdb);
275-
m.setQuery(QString("SELECT COUNT(`%1`) FROM `%2` WHERE `%3` IS NULL;").arg(pdb->getObjectByName(curTable).table.rowidColumn()).arg(curTable).arg(field->name()));
275+
m.setQuery(QString("SELECT COUNT(%1) FROM %2 WHERE %3 IS NULL;")
276+
.arg(sqlb::escapeIdentifier(pdb->getObjectByName(curTable).table.rowidColumn()))
277+
.arg(sqlb::escapeIdentifier(curTable))
278+
.arg(sqlb::escapeIdentifier(field->name())));
276279
if(m.data(m.index(0, 0)).toInt() > 0)
277280
{
278281
// There is a NULL value, so print an error message, uncheck the combobox, and return here
@@ -296,7 +299,10 @@ void EditTableDialog::itemChanged(QTreeWidgetItem *item, int column)
296299
if(!m_bNewTable)
297300
{
298301
SqliteTableModel m(this, pdb);
299-
m.setQuery(QString("SELECT COUNT(*) FROM `%1` WHERE `%2` <> CAST(`%3` AS INTEGER);").arg(curTable).arg(field->name()).arg(field->name()));
302+
m.setQuery(QString("SELECT COUNT(*) FROM %1 WHERE %2 <> CAST(%3 AS INTEGER);")
303+
.arg(sqlb::escapeIdentifier(curTable))
304+
.arg(sqlb::escapeIdentifier(field->name()))
305+
.arg(sqlb::escapeIdentifier(field->name())));
300306
if(m.data(m.index(0, 0)).toInt() > 0)
301307
{
302308
// There is a non-integer value, so print an error message, uncheck the combobox, and return here
@@ -337,14 +343,14 @@ void EditTableDialog::itemChanged(QTreeWidgetItem *item, int column)
337343
{
338344
// Because our renameColumn() function fails when setting a column to unique when it already contains the same values
339345
SqliteTableModel m(this, pdb);
340-
m.setQuery(QString("SELECT COUNT(`%2`) FROM `%1`;").arg(curTable).arg(field->name()));
346+
m.setQuery(QString("SELECT COUNT(%2) FROM %1;").arg(sqlb::escapeIdentifier(curTable)).arg(sqlb::escapeIdentifier(field->name())));
341347
int rowcount = m.data(m.index(0, 0)).toInt();
342-
m.setQuery(QString("SELECT COUNT(distinct `%2`) FROM `%1`;").arg(curTable).arg(field->name()));
348+
m.setQuery(QString("SELECT COUNT(DISTINCT %2) FROM %1;").arg(sqlb::escapeIdentifier(curTable)).arg(sqlb::escapeIdentifier(field->name())));
343349
int uniquecount = m.data(m.index(0, 0)).toInt();
344350
if(rowcount != uniquecount)
345351
{
346352
// There is a NULL value, so print an error message, uncheck the combobox, and return here
347-
QMessageBox::information(this, qApp->applicationName(), tr("Column `%1` has no unique data.\n").arg(field->name())
353+
QMessageBox::information(this, qApp->applicationName(), tr("Column '%1'' has no unique data.\n").arg(field->name())
348354
+ tr("This makes it impossible to set this flag. Please change the table data first."));
349355
item->setCheckState(column, Qt::Unchecked);
350356
return;

‎src/ExportCsvDialog.cpp‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -210,7 +210,7 @@ void ExportCsvDialog::accept()
210210
{
211211
// if we are called from execute sql tab, query is already set
212212
// and we only export 1 select
213-
QString sQuery = QString("SELECT * from `%1`;").arg(selectedItems.at(i)->text());
213+
QString sQuery = QString("SELECT * FROM %1;").arg(sqlb::escapeIdentifier(selectedItems.at(i)->text()));
214214

215215
exportQuery(sQuery, filenames.at(i));
216216
}

‎src/ImportCsvDialog.cpp‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -223,7 +223,7 @@ void ImportCsvDialog::accept()
223223
it != csv.csv().end();
224224
++it)
225225
{
226-
QString sql = QString("INSERT INTO `%1` VALUES(").arg(ui->editName->text());
226+
QString sql = QString("INSERT INTO %1 VALUES(").arg(sqlb::escapeIdentifier(ui->editName->text()));
227227

228228
QStringList insertlist;
229229
for(QStringList::const_iterator jt = it->begin(); jt != it->end(); ++jt)
@@ -321,7 +321,7 @@ void ImportCsvDialog::updatePreview()
321321
void ImportCsvDialog::checkInput()
322322
{
323323
bool valid = true;
324-
if(ui->editName->text().isEmpty() || ui->editName->text().contains("`"))
324+
if(ui->editName->text().isEmpty())
325325
valid = false;
326326

327327
ui->buttonBox->button(QDialogButtonBox::Ok)->setEnabled(valid);

‎src/MainWindow.cpp‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -348,7 +348,7 @@ void MainWindow::populateTable(const QString& tablename)
348348
v.push_back(format);
349349
only_defaults = false;
350350
} else {
351-
v.push_back("`" + db.getObjectByName(tablename).table.fields().at(i)->name() + "`");
351+
v.push_back(sqlb::escapeIdentifier(db.getObjectByName(tablename).table.fields().at(i)->name()));
352352
}
353353
}
354354
if(only_defaults)
@@ -652,7 +652,7 @@ void MainWindow::deleteObject()
652652
QMessageBox::Yes, QMessageBox::No | QMessageBox::Default | QMessageBox::Escape) == QMessageBox::Yes)
653653
{
654654
// Delete the table
655-
QString statement = QString("DROP %1 `%2`;").arg(type.toUpper()).arg(table);
655+
QString statement = QString("DROP %1 %2;").arg(type.toUpper()).arg(sqlb::escapeIdentifier(table));
656656
if(!db.executeSQL( statement))
657657
{
658658
QString error = tr("Error: could not delete the %1. Message from database engine:\n%2").arg(type).arg(db.lastErrorMessage);

‎src/SqlExecutionArea.cpp‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -100,7 +100,7 @@ void SqlExecutionArea::saveAsView()
100100
return;
101101

102102
// Create the view
103-
if(db->executeSQL(QString("CREATE VIEW `%1` AS %2;").arg(name).arg(model->query())))
103+
if(db->executeSQL(QString("CREATE VIEW %1 AS %2;").arg(sqlb::escapeIdentifier(name)).arg(model->query())))
104104
QMessageBox::information(this, qApp->applicationName(), tr("View successfully created."));
105105
else
106106
QMessageBox::warning(this, qApp->applicationName(), tr("Error creating view: %1").arg(db->lastErrorMessage));

‎src/VacuumDialog.cpp‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -55,7 +55,7 @@ void VacuumDialog::accept()
5555
// No, so execute a vacuum command for each selected object individually
5656
QList<QTreeWidgetItem*> selection = ui->treeSelectedObjects->selectedItems();
5757
foreach(QTreeWidgetItem* item, selection)
58-
db->executeSQL(QString("VACUUM `%1`;").arg(item->text(0)), false);
58+
db->executeSQL(QString("VACUUM %1;").arg(sqlb::escapeIdentifier(item->text(0))), false);
5959
}
6060

6161
QApplication::restoreOverrideCursor();

0 commit comments

Comments
 (0)