Using a variable in a mysql query, in a C++ MFC program

Question

After extensive trawling of the internet, I still haven't found any solution for this problem.

I'm writing a small C++ app that connects to an online database and outputs the data in a listbox.

I need to enable a search function using an edit box, but I can't get the query to work while using a variable.

My code is:

res = mysql_perform_query (conn, "select distinct artist from Artists");
//res = mysql_perform_query (conn, "select album from Artists where artist = ' ' ");



while((row = mysql_fetch_row(res)) != NULL){
    CString str;
    UpdateData();

    str = ("%s\n", row[0]);

    UpdateData(FALSE);
    m_list_control.AddString(str);

}

The first "res =" line is working fine, but I need the second one to work. I have a member variable m_search_edit set up for the edit box, but any way that I try to include it in the sql statement causes errors.

eg.

res = mysql_perform_query (conn, "select album from Artists where artist = '"+m_search_edit+" ' ");

causes this error:

error C2664: 'mysql_perform_query' : cannot convert parameter 2 from 'class CString' to 'char *' No user-defined-conversion operator available that can perform this conversion, or the operator cannot be called"

And when I convert m_search_edit to a char* it gives me a " Cannot add 2 pointers" error.

Is there any way around this?

By the way, you're setting yourself up for SQL injection. Maybe not a huge deal for desktop applications, but you should encode m_search_edit properly nonetheless. — Thomas
No, sprintf was not working and when I removed it the program worked. Sorry I should have been more clear this is a MFC APP. — D.Gaughan
On the SQL injection problem, its just a small project for college, so i`m not too worried about that aspect at the minute, just trying to get the program working first. Thanks for the help though, I wouldnt have had any idea what SQL injection was without it. — D.Gaughan

Nick Meyer Nick Meyer · Accepted Answer · 2010-04-25T20:05:11

The problem here is that you are probably building for Unicode, which means that CString consists of wide characters. You can't directly concatenate an ASCII string with a wide character string (and you can't concatenate string literals with the + operator either).

I think the clearest way to build the query string here is by using the CT2CA macro to convert the contents of the edit control from Unicode to ASCII and CStringA::Format to insert them in the string

CStringA query;
query.Format("select album from Artists where artist = '%s'", CT2CA(m_search_edit));
res = mysql_perform_query(conn, query);

And as Thomas pointed out, you should be aware that this leaves the door open for SQL injection...

EDIT: I'm not sure where this mysql_perform_query API comes from, but from the error message you posted it looks like it also requires a writable buffer (char * instead of const char *). Since I can't find documentation for it, I don't know how big it expects that buffer to be, but to get a modifiable buffer out of a CString, look into the GetBuffer() and ReleaseBuffer() methods:

CStringA query;
query.Format(...); // Replace ... with parameters from above
char * buffer = query.GetBuffer(MAX_STRING_LENGTH); // make length big enough for mysql
res = mysql_perform_query(conn, buffer)
query.ReleaseBuffer();

EDIT2 (in response to latest comment):

Thank you for providing the definition of your mysql_perform_query function. When asking questions in the future, keep in mind it's helpful to know when you've created helper functions like this one.

In this case, your mysql_perform_query function never modifies the query string -- the only thing it does is pass it to mysql_query, which takes a const char *, so there's no reason you shouldn't declare its parameter const too. Once you do that, you'll find my first answer works (no need for GetBuffer/ReleaseBuffer):

MYSQL_RES *mysql_perform_query(MYSQL *conn, const char * query)
{
   // Body as written in comment.
}

Using a variable in a mysql query, in a C++ MFC program

1 Answers