Deleting row from QTableWidget and from Sqlite database

Risver

@SGaist
Okay and can i replace the old data with new data ?

JonB

@Risver
What "new data", from where? Do you mean something like over-pasting a selected column's data with other data in the QTreeView UI? Do you mean updating all rows to change one column's data in the model and/or the SQL database? Programatically or by user interaction? Or what?

Risver

@JonB said in Deleting row from QTableWidget and from Sqlite database:

@Risver
What "new data", from where? Do you mean something like over-pasting a selected column's data with other data in the QTreeView UI? Do you mean updating all rows to change one column's data in the model and/or the SQL database? Programatically or by user interaction? Or what?

I mean updating all rows to change one column's data in the model(not in SQL), I would like this to happen programatically.

The passwords in database are encrypted, I want them to be decrypted when I open the application.

JonB

@Risver
The you must loop over each row, updating the column:

for (int row = 0; row < querymodel->rowCount(); row++)
    querymodel->setData(querymodel->index(row, someColumnNumber), someValue);

Is that what you meant?

Risver

@JonB said in Deleting row from QTableWidget and from Sqlite database:

@Risver
The you must loop over each row, updating the column:
for (int row = 0; row < querymodel->rowCount(); row++)
    querymodel->setData(querymodel->index(row, someColumnNumber), someValue);
Is that what you meant?

Yes, thank you. But it is also changing the data in database.

JonB

@Risver
Of course. If you change a QSqlTableModel's data it will end up updating the database. That's the point of it.

You need to think out what you do and do not want to update, when and why.

Risver

@JonB
Now i'm using

ui->tableView->model()->setData(ui->tableView->model()->index(i, 3), passdecoded);

But it's only work for first row.

    for(int i = 0; i < table->rowCount(); i++)
    {
        QByteArray pass = ui->tableView->model()->data(ui->tableView->model()->index(i, 3)).toString().toUtf8();
        pass = QByteArray::fromBase64(pass);
        QString passdecoded = decoding(pass);
        qDebug() << passdecoded;
        ui->tableView->model()->setData(ui->tableView->model()->index(i, 3), passdecoded);
    }

Now i see its also changing the data in database...

JonB

@Risver
Read https://doc.qt.io/qt-5/qsqltablemodel.html#submitAll, https://doc.qt.io/qt-5/qsqltablemodel.html#EditStrategy-enum and https://doc.qt.io/qt-5/qsqltablemodel.html#setEditStrategy, and check what yours currently is.

Risver

@JonB said in Deleting row from QTableWidget and from Sqlite database:

@Risver
Read https://doc.qt.io/qt-5/qsqltablemodel.html#submitAll, https://doc.qt.io/qt-5/qsqltablemodel.html#EditStrategy-enum and https://doc.qt.io/qt-5/qsqltablemodel.html#setEditStrategy, and check what yours currently is.

I'm not sure, before i used setEditStrategy() only the first row was changed, when i used OnFieldChange, all was changed but it was applied to database, when i used OnRowChange and OnManualSubmit only the last row was changed.

JonB

@Risver This is over to you now.

Risver

@JonB said in Deleting row from QTableWidget and from Sqlite database:

@Risver This is over to you now.

Thank you for your help, but I already solved the problem myself. Passwords are decrypted when the application is turned on, and encrypted when closed.

JonB

@Risver That sounds like nothing to do with your model/database/update issues, which should now be working correctly.

SGaist

@Risver said in Deleting row from QTableWidget and from Sqlite database:

@JonB said in Deleting row from QTableWidget and from Sqlite database:

@Risver This is over to you now.

Thank you for your help, but I already solved the problem myself. Passwords are decrypted when the application is turned on, and encrypted when closed.

Base64 is not encryption. It's just encoding. I will be blunt: your password management is just plain wrong. Passwords shall be encrypted with a real encryption algorithm and they are never decrypted because it's a one way algorithm. If you want to avoid troubles in the future, take your time to properly implement that part.

Risver

@SGaist said in Deleting row from QTableWidget and from Sqlite database:

@Risver said in Deleting row from QTableWidget and from Sqlite database:

@JonB said in Deleting row from QTableWidget and from Sqlite database:

@Risver This is over to you now.

Thank you for your help, but I already solved the problem myself. Passwords are decrypted when the application is turned on, and encrypted when closed.

Base64 is not encryption. It's just encoding. I will be blunt: your password management is just plain wrong. Passwords shall be encrypted with a real encryption algorithm and they are never decrypted because it's a one way algorithm. If you want to avoid troubles in the future, take your time to properly implement that part.

I'm using "qaesencryption", the passwords are safe.

SGaist

@Risver said in Deleting row from QTableWidget and from Sqlite database:

Passwords are decrypted when the application is turned on, and encrypted when closed.

This is not safe.

Risver

@SGaist
I realize this, but i have no idea how to do it differently. I don't know how to download the passwords from db and send them to the tableView without updating the database.

JonB

@Risver said in Deleting row from QTableWidget and from Sqlite database:

I don't know how to download the passwords from db and send them to the tableView without updating the database.

I don't know if you wish to discuss this any further, but if you do you would have to explain what this means, and why it has anything to do with the view (rather than the model), plus where updating the database is involved.

When you put passwords into storage (like a database) there are two possible approaches, depending on what you need to do with the passwords:

If you need to decrypt them for some purpose (e.g. you have to pass it on in clear-text to something else) you have to use a symmetric (two-way) encryption algorithm, and that is inherently unsafe. Anyone else armed with the decryption algorithm can discover what the original clear-text password was.
But if you only to check user input against a previously supplied password, you do not need to decrypt, and you should use an asymmetric (one-way) algorithm. You store the previously-encrypted password and when the user supplies the password to check you only encrypt that as you did when storing, and compare that against what was stored. Either the encrypted versions are the same or they are not. This way end users/hackers cannot discover the saved password by decrypting, because that is not available, they simply have to supply the right password so that it is correct when encrypted only. For example, your SQL password or OS logon password is stored this way.

Risver

@JonB said in Deleting row from QTableWidget and from Sqlite database:

@Risver said in Deleting row from QTableWidget and from Sqlite database:

I don't know how to download the passwords from db and send them to the tableView without updating the database.

I don't know if you wish to discuss this any further, but if you do you would have to explain what this means, and why it has anything to do with the view (rather than the model), plus where updating the database is involved.

When you put passwords into storage (like a database) there are two possible approaches, depending on what you need to do with the passwords:

If you need to decrypt them for some purpose (e.g. you have to pass it on in clear-text to something else) you have to use a symmetric (two-way) encryption algorithm, and that is inherently unsafe. Anyone else armed with the decryption algorithm can discover what the original clear-text password was.

But if you only to check user input against a previously supplied password, you do not need to decrypt, and you should use an asymmetric (one-way) algorithm. You store the previously-encrypted password and when the user supplies the password to check you only encrypt that as you did when storing, and compare that against what was stored. Either the encrypted versions are the same or they are not. This way end users/hackers cannot discover the saved password by decrypting, because that is not available, they simply have to supply the right password so that it is correct when encrypted only. For example, your SQL password or OS logon password is stored this way.

It's the password manager so I need to use symetric encrpytion(In my case it is AES-256-CBC).
Look at my code.

#include <QMessageBox>
#include <QtGui>
#include <QAbstractItemModel>
#include <QCryptographicHash>
#include <QFileDialog>
#include <openssl/aes.h>

#include "mainwindow.h"
#include "ui_mainwindow.h"


MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this); 
    setWindowFlags(Qt::MSWindowsFixedSizeDialogHint);

    m_logWindow = new login(this);
    m_regWindow = new signup(this);
    m_genWindow = new generator(this);

    connect(m_logWindow,&login::LoginSuccess, this, &MainWindow::nextWindow);
};

MainWindow::~MainWindow()
{
    encodedLine();
    delete ui;
}

void MainWindow::on_login_clicked()
{
    m_logWindow->show();
}

void MainWindow::on_signup_clicked()
{
    m_regWindow->show();
}

//Manager

void MainWindow::nextWindow(QString base)
{
    ui->stackedWidget->setCurrentIndex(1);
    Bufferbase = base;

    m_db = QSqlDatabase::addDatabase("QMYSQL");
    m_db.setHostName("localhost");
    m_db.setUserName("root");
    m_db.setPassword("");
    m_db.setDatabaseName("DiffPass");

    if(!m_db.open())
        qDebug() << m_db.lastError();

    table = new QSqlTableModel(this, m_db);

    table->setTable(Bufferbase);
    table->select();
    ui->tableView->setModel(table);
    ui->tableView->setColumnHidden (0,true);
    decodedLine();
}

void MainWindow::on_remove_clicked()
{
    if (ui->tableView->selectionModel()->hasSelection())
    {
        int addressId = ui->tableView->selectionModel()->currentIndex().row();
        table->removeRows(addressId, 1);
        table->submitAll();
        refresh();
    }
}

void MainWindow::on_add_clicked()
{
    QString url = ui->recordUrl->text();
    QString email = ui->recordMail->text();
    QString password = ui->recordPass->text();

    QSqlQuery qry(m_db);

    qry.prepare("INSERT INTO "+Bufferbase+"(url, email, password)" "VALUES(:url, :email, :password)");
    qry.bindValue(":url", url);
    qry.bindValue(":email", email);
    qry.bindValue(":password", password);

    if(qry.exec())
    {
        ui->recordUrl->clear();
        ui->recordMail->clear();
        ui->recordPass->clear();
        table->submitAll();
        refresh();
    }

    else
    {
        qDebug() << qry.lastError();
    }
}

void MainWindow::on_tableView_clicked(const QModelIndex &index)
{
    int row = index.row();
    int column = index.column();
    QString text = index.sibling(row, column).data().toString();
    auto clipboard = QGuiApplication::clipboard();
    clipboard->setText(text);
}

QByteArray MainWindow::encrypt(QByteArray plainText)
{
    QByteArray passphrase = QByteArray::fromBase64("pE4B5J5u18U55BTJho//Fioy2bEURa5W/o7HrO1O7/s=");
    QByteArray myiv = QByteArray::fromBase64("Gi9kSb/a5f0h7Mb+sRWQdQ==");

    unsigned char* Key = (unsigned char*) passphrase.data();
    unsigned char* IV = (unsigned char*) myiv.data();

    AES_KEY* AesKey = new AES_KEY();
    AES_set_encrypt_key(Key, 256, AesKey);

    const int UserDataSize = (const int)plainText.size();
    int RequiredPadding = (AES_BLOCK_SIZE - (UserDataSize % AES_BLOCK_SIZE));
    for(int i=0; i < RequiredPadding; i++) {
        plainText.push_back('\0');
    }

    unsigned char * UserData = (unsigned char*) plainText.data();
    const int UserDataSizePadded = (const int)plainText.size();

    unsigned char EncryptedData[UserDataSizePadded] = {0};
    AES_cbc_encrypt(UserData, EncryptedData, UserDataSizePadded, (const AES_KEY*)AesKey, IV, AES_ENCRYPT);

    QByteArray encrypted = QByteArray(reinterpret_cast<char*>(EncryptedData), UserDataSizePadded);

    return encrypted;
}

QByteArray MainWindow::decrypt(QByteArray encryptedText)
{
    QByteArray passphrase = QByteArray::fromBase64("pE4B5J5u18U55BTJho//Fioy2bEURa5W/o7HrO1O7/s=");
    QByteArray myiv = QByteArray::fromBase64("Gi9kSb/a5f0h7Mb+sRWQdQ==");

    unsigned char* Key = (unsigned char*) passphrase.data();
    unsigned char* IV = (unsigned char*) myiv.data();

    AES_KEY* AesDecryptKey = new AES_KEY();
    AES_set_decrypt_key(Key, 256, AesDecryptKey);

    unsigned char DecryptedData[encryptedText.size()] = {0};
    AES_cbc_encrypt((unsigned char*) encryptedText.data(), DecryptedData, encryptedText.size(), (const AES_KEY*)AesDecryptKey, IV, AES_DECRYPT);

    QByteArray decrypted = QByteArray(reinterpret_cast<char*>(DecryptedData), encryptedText.size());

    return decrypted;
}

void MainWindow::decodedLine()
{
    for(int row = 0; row < table->rowCount(); row++)
    {
        QByteArray pass = ui->tableView->model()->data(ui->tableView->model()->index(row, 3)).toString().toUtf8();
        pass = QByteArray::fromBase64(pass);
        QString passdecoded = decryptpass);
        ui->tableView->model()->setData(ui->tableView->model()->index(row, 3), passdecoded);
        table->submitAll();
    }
}

void MainWindow::encodedLine()
{
    for(int row = 0; row < table->rowCount(); row++)
    {
        QByteArray pass = ui->tableView->model()->data(ui->tableView->model()->index(row, 3)).toString().toUtf8();
        QByteArray passencoded = encrypt(pass).toBase64();
        ui->tableView->model()->setData(ui->tableView->model()->index(row, 3), passencoded);
        table->submitAll();
    }
}

void MainWindow::refresh()
{
    table->setTable(Bufferbase);
    table->select();
    ui->tableView->setModel(table);
    ui->tableView->setColumnHidden (0,true);
}

I'm using tableView->model() to set the data because i couldn't find the function to set the data only in View(not SQL table) in tableView class.

JonB

@Risver said in Deleting row from QTableWidget and from Sqlite database:

It's the password manager so I need to use symetric encrpytion(In my case it is AES-256-CBC).

Sorry, I don't know what "It's the password manager so ..." is about. Maybe someone else understands.

Look at my code.

Seeing code does not affect anything. Either you need to decrypt to pass a password on in plain text or you do not. You presumably know that.

qry.prepare("INSERT INTO "+Bufferbase+"(url, email, password)" "VALUES(:url, :email, :password)");

If you are storing a password that will be used to pass to some emailing command then obviously you will need to be able to pass that in clear text.

I'm using tableView->model() to set the data because i couldn't find the function to set the data only in View(not SQL table) in tableView class.

I don't know what you have in mind here. Either a tableview is bound to a database to show its data or it is not. There isn't a " function to set the data only in View(not SQL table) in tableView class."

As I said earlier, if you are happy with your code/usage that is up to you. I was merely explaining the two usages of password storage.

SGaist

@JonB I think that @Risver is writing a password manager.