Prepared Statements in C

This is a summarization of information from the MySQL C API Documentation linked below:

Initialize and connect the database using mysql_init() and a MYSQL object. If you pass NULL into the initialization, the function will allocate, initialize, and return a new object.
Call mysql_real_connect() to establish a connection passing in the necessary parameters (listed here). Client programs must successfully connect to a server before executing any other API functions that require a valid MYSQL connection handler structure.
To get started, obtain a statement handler by passing a MYSQL connection handler to mysql_stmt_init(), this returns a pointer to a MYSQL_STMT data structure.
Specify the statement to prepare by passing the MYSQL_STMT pointer and statement string to mysql_stmt_prepare().
Provide the input parameters for the statement by using the MYSQL_BIND structure and passing them to mysql_stmt_bind_param(). For additional support on setting the bind structure please visit the link at the bottom of this section:
Execute the statement by calling mysql_stmt_execute(), This will execute the prepared query associated with the statement handler. The bound parameter marker values are also sent to the server, and lastly, the server replaces the markers with this newly supplied data (Thus preventing a(n) SQL injection attack).

For error handling any of these function calls please click on the function in question and scroll to the bottom of the page where it says "Return Values".

There will also be an example with error handling below.

FULL EXAMPLE:

MYSQL *conn = mysql_init(NULL);                               [1]
mysql_real_connect(conn, "db", "root",                        [2]
"letmein","SocialMediaApp", 3306, NULL, 0);
                                
...

stmt = mysql_stmt_init(conn);                                 [3]
char *insert_sql = "INSERT INTO table VALUES (?, ?);";        [3]
MYSQL_STMT *stmt;                                             [3]

...

mysql_stmt_prepare(stmt, insert_sql, strlen(insert_sql));     [4]

...

MYSQL_BIND bind[PARAMS];                                      [5]
memset(bind, 0, sizeof(bind));                                [5]

unsigned long param1_len = strlen(param1);                    [5]
bind[0].buffer_type = MYSQL_TYPE_STRING;                      [5]
bind[0].buffer = param1;                                      
bind[0].buffer_length = strlen(param1);                       
bind[0].is_null = 0;                                          
bind[0].length = &param1_len;                                 

unsigned long param2 = strlen(param2);                        [5]
bind[1].buffer_type = MYSQL_TYPE_STRING;                      [5]
bind[1].buffer = param2;                                      
bind[1].buffer_length = strlen(param2);                       
bind[1].is_null = 0;                                          
bind[1].length = &param2_len;                                 

mysql_stmt_bind_param(stmt, bind);                            [5]

...

mysql_stmt_execute(stmt);                                     [6]

FULL EXAMPLE WITH ERROR HANDLING:

MYSQL *conn = mysql_init(NULL);                               [1]
if (conn == NULL) {
      fprintf(stderr, "mysql_init: %s\n", mysql_error(conn));
      exit(1);
}

if (mysql_real_connect(conn, "db", "root",                    [2]
"letmein","SocialMediaApp", 3306, NULL, 0) == NULL) {         
    fprintf(stderr, mysql_errno(conn));                     
    exit(1);
}
                               
...

stmt = mysql_stmt_init(conn);                                 [3]
if (!stmt) {                                                  
    fprintf(stderr, " mysql_stmt_init(), out of memory\n");   
    exit(0);       
}                                 

char *insert_sql = "INSERT INTO table VALUES (?, ?);";        [3]
MYSQL_STMT *stmt;                                             

...

if (mysql_stmt_prepare(stmt, insert_sql, strlen(insert_sql))) [4]
{
    fprintf(stderr, " mysql_stmt_prepare(), INSERT failed\n");
    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));         
    exit(0);
}

...

MYSQL_BIND bind[PARAMS];                                      [5]
memset(bind, 0, sizeof(bind));                                [5]

unsigned long param1_len = strlen(param1);                    [5]
bind[0].buffer_type = MYSQL_TYPE_STRING;                      [5]
bind[0].buffer = param1;                                      
bind[0].buffer_length = strlen(param1);                       
bind[0].is_null = 0;                                          
bind[0].length = &param1_len;                                 

unsigned long param2 = strlen(param2);                        [5]
bind[1].buffer_type = MYSQL_TYPE_STRING;                      [5]
bind[1].buffer = param2;                                      
bind[1].buffer_length = strlen(param2);                       
bind[1].is_null = 0;                                          
bind[1].length = &param2_len;                                 

if (mysql_stmt_bind_param(stmt, bind)) {                      [5]
    fprintf(stderr, " mysql_stmt_bind_param() failed\n");     
    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));         
    exit(0);
}

...

if (mysql_stmt_execute(stmt)) {                               [6]
    fprintf(stderr, " mysql_stmt_execute(), 1 failed\n");
    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
    exit(0);
}

int status = mysql_stmt_fetch(stmt);                   [Fetch-status]

if (status == 1 || status == MYSQL_NO_DATA) {
    return false;
} else {
    return true;
}

Prepared Statements in C

A short description on writing prepared statements in C for lessons like SQL injection.