Hello Lalit, Thank you for an excellent detailed report! I wish all bug reports were like this.
This was a bug which should be fixed as of https://github.com/SOCI/soci/commit/10d2c8ab3843ca9c9c9f1b8c2fa72130d2928b2a . Best regards, Aleksander On Wed, Mar 26, 2014 at 9:11 PM, Lalit Bhasin <lalit_...@yahoo.com> wrote: > Hello Guys, > I am facing some issue in performing insert operation containing escape > sequence using soci library. Same DDL operation is successful when using > mysql backend c library, To show the problem, I have written small > ping-pong code as below: > -- > #include <soci/soci.h> > #include <soci/mysql/soci-mysql.h> > #include <iostream> > #include <istream> > #include <ostream> > #include <string> > #include <exception> > > using namespace soci; > using namespace std; > char* escape_string( soci::session &sql, std::string &str) { > > soci::mysql_session_backend *mysql_backend = > static_cast<soci::mysql_session_backend *>(sql.get_backend()); > const char *i_str = str.c_str(); > unsigned long i_length = str.length(); > unsigned long o_length = i_length * 2 + 1 ; //worst case where we > need to escape all characters. > char *o_str = (char *) malloc( o_length * sizeof(o_length)); //need's > to be CLEANEDUP by calleee > mysql_real_escape_string(mysql_backend->conn_,o_str,i_str, i_length); > return o_str; > } > > int main () { > > soci::session sql("mysql", "service=db user=scott password=tiger"); > std::string c1 = "word1'word2:word3"; > char *o_str = escape_string(sql, c1); > std::string q = "INSERT INTO MY_TEST ( COMMENTS ) VALUE ('"; > q.append(o_str); > q+= "')"; > try { > sql << q; > std::cout << " \nSOCI Insert successful"; > } catch (std::exception &e) { > std::cout << "\n SOCI Exception : " << e.what() << "\n"; > } > soci::mysql_session_backend *mysql_backend = > static_cast<soci::mysql_session_backend *>(sql.get_backend()); > if (mysql_query(mysql_backend->conn_, q.c_str()) ){ > fprintf(stderr, "MYSQL Backend Lib error: %s\n", > mysql_error(mysql_backend->conn_)); > exit(1); > } else { > std::cout << "\n MYSQL Backend Lib Insert successful\n"; > > } > -- > > Here is what I am doing in above code: > - Create an INSERT statement for table MY_TEST. The value for column > COMMENTS contains special character ( single quote), which I am escaping > using mysql_real_escape_string(). > - Do INSERT operation first using soci library and the using mysql > client library. The INSERT operation is successful through mysql client > library, but fails with soci as below: > > --clip------ > [shell]$ ./a.out > > SOCI Exception : 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 ''word1\'word2' at line 1 > > MYSQL Backend Lib Insert successful > ---clap------- > > > > When I check the exact insert statement received by mysql server. Server > received different statements in both cases as below ( first row is for > soci, and second row is for mysql client in below): > > ---clip-- > mysql> select event_time, argument from mysql.general_log where argument > like '%INSERT%'; > > +---------------------+-------------------------------------------------------------------------------------+ > | event_time | argument > | > > +---------------------+-------------------------------------------------------------------------------------+ > | 2014-03-27 11:28:43 | INSERT INTO MY_TEST ( COMMENTS ) VALUE > ('word1\'word2 > | 2014-03-27 11:28:43 | INSERT INTO MY_TEST ( COMMENTS ) VALUE > ('word1\'word2:word3') > > ---------------- > --clap-- > > As you see, soci has truncated some part of the statement before sending > it to server , and hence DDL operation fails. While mysql client library > has parsed the statement correctly and sent it to server. > > > ---clip-- > mysql> select * from MY_TEST; > +-------------------+ > | COMMENTS | > +-------------------+ > | word1'word2:word3 | > +-------------------+ > 1 row in set (0.00 sec) > > > mysql> desc MY_TEST; > +----------+--------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +----------+--------------+------+-----+---------+-------+ > | COMMENTS | varchar(255) | YES | | NULL | | > +----------+--------------+------+-----+---------+-------+ > 1 row in set (0.00 sec) > > > --clap--- > > > I am not very sure if this is soci problem, or if I am doing something > wrong here. Moreover I know the better way to escape the special characters > is using placeholder/prepared statement. But above is just a test code. In > actual scenario, the table name and column names (and number of columns) > are know only during run time and it is not feasible to use placeholder > queries or prepared statement. > > Please let me know if I am doing something wrong, or if it is known issue. > Thanks > in advance for help. > Best Regards, > Lalit > > > > ------------------------------------------------------------------------------ > > _______________________________________________ > soci-users mailing list > soci-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/soci-users > >
------------------------------------------------------------------------------
_______________________________________________ soci-users mailing list soci-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/soci-users
