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
– Thomas, Commented Apr 25, 2010 at 19:57
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
– D.Gaughan, Commented Apr 25, 2010 at 19:59
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
– D.Gaughan, Commented Apr 25, 2010 at 21:50

Nick Meyer · Accepted Answer · 2010-04-26 11:04:47Z

2

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

edited Apr 26, 2010 at 11:04

answered Apr 25, 2010 at 20:05

Nick Meyer

40.7k14 gold badges69 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

D.Gaughan Over a year ago

This doesnt work either. Its giving 5 errors with the "A" left in after "Cstring" and 2 errors if I remove the "A". The two errors are CT2CA: Undeclared Identifier and 'mysql_perform_query' : cannot convert parameter 2 from 'class CString' to 'char *' I should have pointed out in the first post that i`m doing this on VC++ 6.

D.Gaughan Over a year ago

I tried rebuilding the program in Visual Studio 2008. I got this error from your piece of code: Error 1 error C2664: 'mysql_perform_query' : cannot convert parameter 2 from 'CStringA' to 'char *'

Nick Meyer Over a year ago

@D.Gaughan what mysql API is this? I couldn't find documentation for mysql_perform_query on the web, only mysql_query. Anyway, CString only has an implicit conversion to const char *. If you need to get a modifiable buffer for a function that takes a non-const, you should look into GetBuffer() and ReleaseBuffer(). I'm editing my answer.

D.Gaughan Over a year ago

Im using the mysql C++ connector. I have this block of code at the start of the program, I dont know if this is of any use to you though. MYSQL_RES* mysql_perform_query(MYSQL *conn, char *sql_query) { // send the query to the database if (mysql_query(conn, sql_query)) { // printf("MySQL query error : %s\n", mysql_error(conn)); // exit(1); } return mysql_use_result(conn); } The code you posted is still giving errors. The main one is that query.Buffer(...): no overloaded function contains 0 parameters.

Nick Meyer Over a year ago

@D.Gaughan you say query.Buffer(...) gives errors, but that didn't appear in my post. Make sure you get the whole name, *Get*Buffer or *Release*Buffer, or if you're calling query.Format, that you replace the ... with the parameters from the first example (I just cut them out of the second example to keep the focus on the usage of GetBuffer and ReleaseBuffer).

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related