MySQL C API custom values

Question

I have been working with the tutorial on MySQL C API from http://zetcode.com/tutorials/mysqlcapitutorial/ the following example is working fine:

#include <my_global.h>
#include <mysql.h>

int main(int argc, char **argv)
{

MYSQL *conn;

conn = mysql_init(NULL);
mysql_real_connect(conn, "localhost", "zetcode", "passwd", "testdb", 0, NULL, 0);

mysql_query(conn, "CREATE TABLE writers(name VARCHAR(25))");

mysql_query(conn, "INSERT INTO writers VALUES('Leo Tolstoy')");
mysql_query(conn, "INSERT INTO writers VALUES('Jack London')");
mysql_query(conn, "INSERT INTO writers VALUES('Honore de Balzac')");
mysql_query(conn, "INSERT INTO writers VALUES('Lion Feuchtwanger')");
mysql_query(conn, "INSERT INTO writers VALUES('Emile Zola')");

mysql_close(conn);

}

How can i change the code to accept custom values instead of the hardcoded ones, is it possible to replace writers and ex. Leo Tolstoy with a char pointer or something?

Answer 1

You will likely have to compose your strings, e.g. using sprintf().

Answer 2

You can probably use sprintf() / snprintf(), as for example:

#include <my_global.h>
#include <mysql.h>

int main(int argc, char **argv)
{
MYSQL *conn;
conn = mysql_init(NULL);
/* error checking missing */
mysql_real_connect(conn, "localhost", "zetcode", "passwd", "testdb", 0, NULL, 0);
/* error checking missing */
mysql_query(conn, "CREATE TABLE writers(name VARCHAR(25))");
/* error checking missing */
do {
    char cmd[1000];
    char *name = "Leo Tolstoy"; /* get from user or file or something */
    snprintf(cmd, 999, "INSERT INTO writers VALUES('%s')", name);
    /* error checking missing */
    mysql_query(conn, cmd);
    /* error checking missing */
} while (0);
mysql_close(conn);
/* error checking missing */
}

Answer 3

You have basically two options:

• You can construct the query yourself, using sprintf. Then you should use mysql_real_escape_string on all your variables, otherwise your code is vulnerable to SQL injection.
• You can use prepared statements. The documentation for mysql_stmt_execute has some examples. (this is the better option)