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
[email protected]
https://lists.sourceforge.net/lists/listinfo/soci-users
