Lalit Bhasin
2014-03-27 04:11:46 UTC
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
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