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

Question

Hi, after extensive trawling of the internet I still havent 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 cant 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 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.

Any way around this???

Answer 1

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.
}