CODEWITHSUNDEEP
mysqlclinux
Onur Turalı
Onur Turalı

Reputation: 5

How do I insert data into mysql table?

I am trying to insert data into a mysql database. The connection and the displaying of data works.

But I don't know how to use the INSERT INTO table command in C code.

I've tried reading strings with scanf/getchar to the values in the mysql command, but it didn't work.

How do I insert data into the mysql table after I read the data in my program?

I'm working in Linux.

This is my source code:

#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>

static char *host = "localhost";
static char *user = "root";
static char *pass = "PASSWORD";
static char *dbname = "tutorial";

unsigned int port = 3306;
static char *unix_socket = NULL;
unsigned int flag = 0;

int main()
{
MYSQL *conn;
MYSQL_RES * res;
MYSQL_ROW row;
conn = mysql_init(NULL);

if(!(mysql_real_connect(conn, host, user, pass, dbname, port, unix_socket, flag)))
{
    fprintf(stderr, "Error: %s[%d]", mysql_error(conn), mysql_errno(conn));
    exit(1);
}

mysql_query(conn, "SELECT * FROM users");
res = mysql_store_result(conn);


while(row = mysql_fetch_row(res))
{
    printf("%s\t%s\n", row[0], row[1]);
}

mysql_free_result(res);
mysql_close(conn);

return EXIT_SUCCESS;
}

I tried:

...
int id[1] = 5;
char name[8] = "Jack";
...
mysql_query(conn, INSERT INTO users(id, name) VALUES(id, name);
...

Upvotes: 0

Views: 3260

Answers (3)

Pratik Babaso Chougule
Pratik Babaso Chougule

Reputation: 1

Hello So you want to insert data into table but with not directly giving values rather taking input from other source so you can use this type

Here I used snprintf to create a query statement and then execute it

#define MAX_STRING 128;

char curr_uname[32]="gamer";
float curr_ran=0.33;
char curr_url[32]="https://www.google.com";

char query[MAX_STRING] = {0};

snprintf(query, MAX_STRING, "INSERT INTO redirect_database (username, homepage) VALUES ('%s', '%s')", curr_uname, curr_url);

if (mysql_query(conn, query)!= 0)                   
{                                                                                                  
  fprintf(stderr, "Query Failure\n");                                                              
  return EXIT_FAILURE;                                                                             
}

Upvotes: 0

LPs
LPs

Reputation: 16243

You must prepare you C-string before query creation.

You can use snprintf to do so:

#define MAX_STRING 128
char query[MAX_STRING] = {0};
int id = 5;
char name[] = "jack";

snprintf(query, MAX_STRING, "INSERT INTO users (id, name) VALUES (%d, '%s')", id, name);

mysql_query(conn, query);

As @viraptor pointed out the solution above has problems with sql injection. You should use mysql api (coded to be totally safe) to do the job:

#define INSERT_QUERY = "INSERT INTO users (id, name) VALUES (?,?)";

int id = 5;
char name[] = "jack";
size_t str_length = strlen(name);

MYSQL_STMT *stmt = mysql_stmt_init(mysql);
if (!stmt)
{
   fprintf(stderr, " mysql_stmt_init(), out of memory\n");
     exit(0);
}
if (mysql_stmt_prepare(stmt, INSERT_QUERY, strlen(INSERT_QUERY)))
{
   fprintf(stderr, " mysql_stmt_prepare(), INSERT failed\n");
   fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
   exit(0);
}

MYSQL_BIND bind[2];

/* INTEGER PARAM */
/* This is a number type, so there is no need
   to specify buffer_length */
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer= (char *)&id;
bind[0].is_null= 0;
bind[0].length= 0;

/* STRING PARAM */
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= (char *)name;
bind[1].buffer_length= str_length+1;
bind[1].is_null= 0;
bind[1].length= &str_length;

/* Bind the buffers */
if (mysql_stmt_bind_param(stmt, bind))
{
  fprintf(stderr, " mysql_stmt_bind_param() failed\n");
  fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  exit(0);
}

/* Execute the INSERT statement - 2*/
if (mysql_stmt_execute(stmt))
{
  fprintf(stderr, " mysql_stmt_execute, 2 failed\n");
  fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  exit(0);
}

/* Get the total rows affected */
my_ulonglong affected_rows= mysql_stmt_affected_rows(stmt);
fprintf(stdout, " total affected rows(insert 2): %lu\n",
                (unsigned long) affected_rows);

if (affected_rows != 1) /* validate affected rows */
{
  fprintf(stderr, " invalid affected rows by MySQL\n");
  exit(0);
}

/* Close the statement */
if (mysql_stmt_close(stmt))
{
  fprintf(stderr, " failed while closing the statement\n");
  fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  exit(0);
}

You can find reference for this code at this link

Upvotes: 2

viraptor
viraptor

Reputation: 34205

Have a look at examples at https://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-execute.html for information how to do this.

In summary, you create a prepared statement, initialize it with mysql_stmt_prepare, then pass the values via mysql_stmt_bind_param.

You could also construct the text version of the query with values quoted by https://dev.mysql.com/doc/refman/5.7/en/mysql-real-escape-string-quote.html but in that case it's easy to accidentally skip some elements.

Upvotes: 1

Related Questions