C ++和SQLite-如何执行由用户输入形成的查询? [英] C++ And SQLite - How to execute a query formed by user input?

问题描述

我正在开发一个项目，用户可以在其中将记录插入SQLite数据库.查询将通过以下方法自动生成:

I'm working on a project where the user can insert a record into a SQLite database. The query will come generated automatically in the following method:

string ID = "";
string title = "";
string password = "";

cout << "Insert ID:\n";
cin >> ID;
cout << "Insert title of password:\n";
cin >> title;
cout << "Insert password:\n";
cin >> password;

string sql = "INSERT INTO test (ID,title,password) VALUES(" + ID + "," + title + "," + password + ");";

当我尝试编译程序时，出现错误:

When i try to compile the program i get the error:

    classes.h:74:76: error: invalid operands of types ‘const char*’ and ‘const char [2]’ to binary ‘operator+’
   string sql = "INSERT INTO passwords (ID,title,password) VALUES (" + id + "," + title + "," + password 
                                                                            ^
classes.h:78:42: error: invalid operands of types ‘int’ and ‘sqlite3_stmt*’ to binary ‘operator&’
    sqlite3_prepare( db, sql.c_str(), -1 &st, NULL);

似乎他不能接受多个角色.有人可以告诉我如何解决此错误吗?

seem that he can't accept multiple character. Can someone show me how to fix this error?

P.S.我是C ++的新手

P.S. I'm new in c++

感谢您的帮助.谢谢.

完整代码

sqlite3 *db;
sqlite3_stmt * st;
int id = 0;
string title = "";
string password = "";

cout << "Insert ID:\n";
        cin >> id;
        cout << "Insert title of password:\n";
        cin >> title;
        cout << "Insert password:\n";
        cin >> password;

        string sql = "INSERT INTO passwords (ID,title,password) VALUES (" + id + ',' + title + ',' + password + ");";

        if(sqlite3_open("pw.db", &db) == SQLITE_OK)
        {
            sqlite3_prepare( db, sql.c_str(), -1 &st, NULL);
            sqlite3_step( st );
        }
        else
        {
            cout << "Failed to connect\n";
        }
sqlite3_finalize(st);
sqlite3_close(db);

推荐答案

您应避免像这样将用户输入直接插入到SQL命令中，否则用户可能会输入恶意文本，故意更改生成的SQL语句.

You should avoid directly inserting user input into your SQL commands like this, a user could enter malicious text that deliberately alters the resulting SQL statement.

相反，请考虑使用参数绑定，这将使您避免尝试进行字符串连接.您的代码:

Instead, consider using parameter binding, this will allow you to avoid the string concatenation you're attempting to do. Your code:

    string sql = "INSERT INTO passwords (ID,title,password) VALUES (" + id + ',' + title + ',' + password + ");";

    if(sqlite3_open("pw.db", &db) == SQLITE_OK)
    {
        sqlite3_prepare( db, sql.c_str(), -1 &st, NULL);
        sqlite3_step( st );
    }

成为

    string sql = "INSERT INTO passwords (ID,title,password) VALUES (?,?,?)";

    if(sqlite3_open("pw.db", &db) == SQLITE_OK)
    {
        sqlite3_prepare( db, sql.c_str(), -1 &st, NULL);
        sqlite3_bind_int(st, 1, ID);
        sqlite3_bind_text(st, 2, title.c_str(), title.length(), SQLITE_TRANSIENT);
        sqlite3_bind_text(st, 3, password.c_str(), password.length(), SQLITE_TRANSIENT);
        sqlite3_step( st );
    }

1 ， 2 和 3 是基于1的参数索引.参见 https://www.sqlite.org/c3ref/bind_blob.html

the 1, 2 and 3 are 1-based parameter indexes. See https://www.sqlite.org/c3ref/bind_blob.html

这篇关于C ++和SQLite-如何执行由用户输入形成的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！