How to setup prepared statements for mysql queries

Question

I'm trying to set up a prepared statement based on this example I found on the web. I just want to protect against sql injections in the grade= and username=, but the statement isn't executing.

MYSQL_STMT *stmt;
MYSQL_BIND bind[2];
char* usrname = &uname[0]; //uname supplied by user
char* choi = choice; //choice supplied by user
stmt = mysql_stmt_init(connect);

char* statement = "UPDATE grades SET grade='?' WHERE username='?'";
mysql_stmt_prepare(stmt, statement, strlen(statement));

memset(bind,0,sizeof(bind));


bind[0].buffer_type=MYSQL_TYPE_STRING;
bind[0].buffer=usrname;
bind[0].buffer_length=50;
bind[0].is_null=0;
bind[0].length= strlen(usrname);

bind[1].buffer_type=MYSQL_TYPE_STRING;
bind[1].buffer=choi;
bind[1].buffer_length=50;
bind[1].is_null=0;
bind[1].length= 2;

mysql_stmt_bind_param(stmt, bind);
mysql_stmt_execute(stmt);

Answer 1

bind[0].length= strlen(usrname);
bind[1].length= 2;

bind[x].length is a pointer. It should be pointing to an address that contains the value of the length, or be NULL (zero) to be "ignored" When set to NULL (or zero), I think length_value is then used... but I don't know if the user should set it, or if it's for mysql to use it internally. However, from what I understood (I didn't try it) this is for getting the length of a string read from the database, so it's not useful in this case because you are not reading.

For input, which is your case, buffer_length is the length of the string you want to send. For output, it is the size of the buffer that can be filled with the value read from the db, which is truncated if longer than that value.

You should have:

bind[0].buffer_type=MYSQL_TYPE_STRING;
bind[0].buffer=usrname;
bind[0].buffer_length=strlen(usrname);
bind[0].is_null=0;
bind[0].length=0;

bind[1].buffer_type=MYSQL_TYPE_STRING;
bind[1].buffer=choi;
bind[1].buffer_length=strlen(choi);
bind[1].is_null=0;
bind[1].length=0;

but I haven't tested that and you must have actual strings in your buffers. The buffers in the sample you provided are not initialized.

I also strongly suggest that you use

 memset(bind, 0, sizeof(bind));

to zero the whole structure. It will avoid having garbage values where you didn't set anything.

This may be useful: https://www.assembla.com/code/DelphiMS/subversion/nodes/Required%20Units/Zeos%207/plain/mysql_bind.txt?rev=32