c++ mysql setString not replacing placeholders in prepared statement

Question

I have the following code to set default values:

int main()
{
    try 
    {
        sql::Driver *driver;
        sql::Connection *con;
        sql::PreparedStatement *pstmt;

        /* Create a connection */
        driver = get_driver_instance();
        con = driver->connect("tcp://127.0.0.1:3306", "$user", "$password");
        /* Connect to the MySQL test database */
        con->setSchema("db");

        string cols[] = { "email", "phone", "first_lead", "address", "city", "state", "country", "client_id" };
        string filler[] = { "email_address@email.com","7777777777", "YYYY-MM-DD", "street_address", "city", "state", "country", "client_id" };

        pstmt = con->prepareStatement("ALTER table clients modify column ? varchar(255) not null default \'?\'");

        for (int i = 0; i <= 8; ++i)
        {
            pstmt->setString(1, cols[i].c_str());
            pstmt->setString(2, filler[i].c_str());
            pstmt->execute();
        }
        delete pstmt;
        delete con;
    } catch (sql::SQLException &e) 
    {
        cout << "# ERR: SQLException in " << __FILE__;
        cout << "(" << __FUNCTION__ << ") on line "
            << __LINE__ << endl;
        cout << "# ERR: " << e.what();
        cout << " (MySQL error code: " << e.getErrorCode();
        cout << ", SQLState: " << e.getSQLState() <<
            " )" << endl;
    }

    cout << endl;
    system("pause");
    return EXIT_SUCCESS;
}

When I run the code, it hits the pstmt assignment and returns the following error:

# ERR: SQLException in c:\users\ray\documents\visual studio 2015\projects\project3\project3\source.cpp(main) on line 56
# ERR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? varchar(255) not null default '?'' at line 1 (MySQL error code: 1064, SQLState:  ?Σ╥Å  ╠╠╠╠╠╠╠ )

The code doesn't seem to recognize and replace the placeholders. Am I implementing setString incorrectly?

score 2 · Answer 1 · answered Jun 16 '17 at 04:13

2

The exception should be thrown at the unitial prepare -- the rest should never be executed.

Prepared statements don't work this way.

They are not string replacements.

Parameters in prepared statements are for data values only -- not object identifiers or any other part of a SQL statement. Just data values.

You can't, for example, prepare the statement SELECT ? FROM ? and use the parameters "id" and "table1" as though you had queried SELECT id FROM table1;. SQL -- in any database engine -- won't allow that.

Prepared statements are parsed first, then the values are used -- not string interpolated -- when the statement is executed.

answered Jun 16 '17 at 04:13

Michael - sqlbot

169,571
25
353
427

That makes sense as the exception does seem to get thrown at the pstmt. When I try this with a stmt->execute with interpolated strings, I run into a bad_alloc error. Do you have an idea as to what may be causing this? – Ray Foo Jun 18 '17 at 01:36
I would assume, in that case, that you're mis-handling the strings, somehow. Check with valgrind, maybe? – Michael - sqlbot Jun 18 '17 at 01:47

c++ mysql setString not replacing placeholders in prepared statement

1 Answers1