C++ and MySQL - how to include variable in my query?

Question

I think I may be missing something really obvious here, but I have been struggling for way too long on this and my C++ is way rusty (10yrs+)

The code below works fine, but I need to be able to pass a variable into the query for lname. If I build the query in a string or char array I get an error that it is not compatible with parameter type of SQLWCHAR*

I know the code below is vulnerable to sql injection, but this is a one time hit on an isolated system, so I am really looking for simplicity more than anything else...

SQLHENV env;
SQLHDBC dbc;
SQLHSTMT  sql_hStmt;
SQLRETURN ret;
SQLWCHAR outstr[1024];
SQLSMALLINT outstrlen;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

ret = SQLDriverConnect(dbc, NULL, L"DSN=myDSN", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &sql_hStmt);
SQLWCHAR* SQL = L"select * from DB.employees where lname='Smith'";
ret = SQLExecDirect(sql_hStmt, SQL, SQL_NTS);

SQLFetch(sql_hStmt);

Answer 1

Thank you to The Beast. If you are recording two or more data, it can be used as follows.

int state;    
string mesafe = to_string(getdata1);
string aci = to_string(getdata2);
string query = "INSERT INTO tarama (mesafe,aci) VALUES (\"";
            query += mesafe;
            query += "\",\"";
            query += aci;
            query += "\")";
qstate = mysql_query(conn, query.c_str());

I couldn't get over this problem for days. Now the problem is solved.

Answer 2

You can simply do it by :

You can do it by

   int var = 10;
   string str = to_string(var);
   string requete="INSERT INTO stat(temps) VALUES (\"";
   requete += str;
   requete += "\")";
   mysql_query(&mysql,requete.c_str());

just specify in mySql that the field has a type of int , double , float etc.

Answer 3

I suggest you PREPARE a paramererized query string. See here

If you simply concatenate strings to build a new query each time, you may be leaving your site open to an SQL injection attack

Answer 4

There are two problems here, one is constructing a string containing the query you want, the other is passing that string as an argument to the function.

My recommendation is to stay as "C++" as possible until you reach these C boundaries. So we should use std::wstring for the string processing up until the point where it needs to be a C-style string:

std::wstring statementText = L"select * from DB.employees where lname='Smith'";
ret = SQLExecDirect(sql_hStmt, const_cast<SQLWCHAR*>(statementText.c_str()), SQL_NTS);

The c_str() member function returns a pointer to a null-terminated array (i.e., a C-style string), but this pointer has the type const wchar_t*; that is, the contents of this C-style string cannot be modified.

This is a problem because SQLWCHAR* is just wchar_t*; it doesn't make any promise to leave the data alone. That is why I included the const_cast, to remove the const from the c_str() value.

This is not something you generally want to do. const_cast is arguably the scariest cast because you directly open up the door to undefined behavior, as it's UB to modify a const object:

const int x = 0;
const int* p = &x; // anyone using this pointer can't modify x

int* bad = const_cast<int*>(p); // but this one is not so good
*bad = 5; // undefined behavior

The reason it's okay here, though, is that SQLExecDirect doesn't actually modify the string it's passed; it's simply an implementation error that const isn't used, so us taking that away is okay. (This lack of const mistake is very common in C.)

If you really need a buffer that can be modified, then starting in the current version of C++ (C++11) you can do this safely:

std::wstring statementText = L"select * from DB.employees where lname='Smith'";
ret = SQLExecDirect(sql_hStmt, &statementText[0], SQL_NTS);

We're taking the address of the first element, which itself is in a null-terminated array; another C-style string. This time, though, we have a modifiable array; the type already matches.

(The reason I make note this is okay in C++11 is that technically in the previous version, C++03, this behavior wasn't guaranteed. It was actually intended to be, but an error in wording in the standard made it not so. To be practical, you're fine either way.)

Whichever one you want to use is up to you. Some will argue to just use &str[0] all the time so we definitely have no UB, I would argue to document your intent and belief that the function doesn't modify the string and cast away const but ultimately operate in a const mindset. If something bad happens it's easy to relax away from const than it is to wish you had put it on.

One important thing to note is that all these returned pointers (either str.c_str() or &str[0]) are only good as long as the str object itself is alive and not modified. This is bad:

const wchar_t* get_query()
{
    std::wstring result = /* build query */;

    // oops, this pointer stops being meaningful when result stops existing!
    return result.c_str();
}

---

With that all out of the way, building up these strings is easy. We have std::wstringstream:

std::wstringstream ss; 

ss << "this is basically an expanding buffer that accepts anything std::wcout will";
ss << std::endl;
ss << "this includes integers " << 5 << " and other stream-insertable types";

So you probably want something like this:

std::wstring build_query(const std::wstring& name)
{
    // you can provide a starting string
    std::wstringstream result(L"select * from DB.employees where lname=");

    result << "\'" << name << "\'";

    return result.str(); // this captures the buffer as a C++ string
}

// Remember, this would be bad!
//
// SQLWCHAR* SQL = const_cast<SQLWCHAR*>(build_query(L"Smith").c_str());
//
// Because the C++ string returned by build_query is temporary;
// it stops existing at the end of this full expression,
// so SQL would be a bad pointer. This is right:

std::wstring SQL = build_query(L"Smith");
ret = SQLExecDirect(sql_hStmt, const_cast<SQLWCHAR*>(SQL.c_str()), SQL_NTS);

Hope that helps.

---

Also, I would avoid using all-upper identifiers except for macros, because such names are overwhelmingly expected to be macros by people reading C++ code. Additionally, I've used C++-style casts in my example code; you should do the same. The C-style cast ((type)value) is simply too powerful to be safe.