How to interact with C++ and database?

In today's information age, the interaction between applications and databases is not only a common problem, but also a necessary problem. C, as a high-level programming language, is widely used in various program development. So how do you use C to interact with a database? This article will introduce you to the steps and basic principles required for C to interact with a database.

1. How to connect to the database in C?

C There are many ways to connect to the database, such as ODBC, ADO, JDBC, etc. Here we take ODBC as an example to illustrate how to interact between C and the database. ODBC, Open Database Connectivity, is a standard interface for connecting to databases. By using ODBC, different applications can use the same database.

1. Install the ODBC driver

The ODBC driver connects to different databases differently. Here we take MySQL as an example. You need to install the MySQL ODBC driver on your computer. You can download the ODBC driver installation package from the MySQL official website and install it. After the installation is complete, you can see the driver in the ODBC Data Source Manager on your computer.

2. Use ODBC to connect to the database

C provides a way for ODBC to perform database operations. You can use the ODBC API provided by Windows API to complete the interaction with the database. To use ODBC, you need to include header files: #include and #include . A sample code for using ODBC to link to a database is as follows:

#include <windows.h>
#include <sql.h>

int main(int argc, char* argv[]) {
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    SQLRETURN retCode;
    char buff[512];

    /*声明句柄并打开ODBC环境*/
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

    /*连接数据库*/
    SQLConnect(dbc, "database_name", SQL_NTS, "user_name", SQL_NTS, "password", SQL_NTS);

    /*执行SQL语句*/
    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    SQLExecDirect(stmt, "SELECT * FROM table_name", SQL_NTS);

    /*处理结果*/
    while(SQLFetch(stmt) == SQL_SUCCESS) {
        SQLGetData(stmt, 1, SQL_C_CHAR, buff, sizeof(buff), NULL);
        cout << "Column1 = " << buff << endl;
    }

    /*释放资源*/
    SQLFreeHandle(SQL_HANDLE_ENV, env);
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);

    return 0;
}

2. How to perform database operations?

After connecting to the database, you can operate the database. When using ODBC for database interaction, the following four functions are mainly involved:

1. SQLAllocHandle(): allocate ODBC handle.

2. SQLConnect(): Connect to the database.

3. SQLExecDirect(): Execute SQL statements.

4. SQLFetch(): Get the results.

The following takes inserting a piece of data as an example to demonstrate how to call several important functions.

1. Insert data

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

retCode = SQLExecDirect(stmt, (SQLCHAR*)"INSERT INTO table_name (Column1, Column2) VALUES ('value1', 'value2')", SQL_NTS);

2. Query data

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

retCode = SQLExecDirect(stmt, (SQLCHAR*)"SELECT * FROM table_name", SQL_NTS);

while(SQLFetch(stmt) == SQL_SUCCESS) {
    SQLGetData(stmt, 1, SQL_C_CHAR, buff, sizeof(buff), NULL);
    cout << "Column1 = " << buff << endl;
}

3. Update data

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

retCode = SQLExecDirect(stmt, (SQLCHAR*)"UPDATE table_name SET Column1='value3' WHERE Column2='value2'",
SQL_NTS);

4. Delete data

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

retCode = SQLExecDirect(stmt, (SQLCHAR*)"DELETE FROM table_name WHERE Column2='value2'",
SQL_NTS);

3. How to avoid SQL injection?

SQL injection attack is a common attack method, which may cause the database to be damaged. In order to avoid SQL injection, C programs need to preprocess and filter user-entered data. It is recommended to use parameterized queries instead of directly splicing SQL statements. The following is an example of a parameterized query:

/*保证输入合法*/
string id = "123";
string name = "Tom' OR '1'='1";   /*SQL注入代码*/

/*通过参数化进行查询*/
SQLCHAR query[255];
sprintf((char*)query, "SELECT * FROM table_name WHERE id=? AND name=?");   /*使用占位符*/

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
SQLPrepare(stmt, query, SQL_NTS);

SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, id.size(), 0, (void*)id.c_str(), 0, NULL);
SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, name.size(), 0, (void*)name.c_str(), 0, NULL);

retCode = SQLExecute(stmt);

4. Conclusion

This article introduces the interaction process between C and the database, and Provides code examples for connecting to the database and basic database operations. It also introduces the basic concepts of SQL injection and how to avoid injection attacks. For C programmers, it is necessary to understand the database connection process, database operation steps and basic knowledge of SQL injection attacks before performing database operations. Only more in-depth research and application of this knowledge can enable us to develop more secure and reliable programs.

The above is the detailed content of How to interact with C++ and database?. For more information, please follow other related articles on the PHP Chinese website!