Escape for insert in sql server

[asav...@jacobacci.com]

I have a connector to a sql server. I've to do a simple insert.
This is the content sent to the connector:

{
"protocol":"JDBC",
"data":{
"DBType":"SQLSERVER_2005",
"sqlUsername":"....",
"sqlPassword":"....",
"sqlStatement":"insert into test${into} values ${values}"
}
}

I've a web interface with an array (myarray) and when the user press a button I prepare the values and I pass them to the connector.
I prepare the values with a javascript in the pre-launch script of the button that starts the process (that do the insert).

This is my script:

function escape_string(str) {
str.replace(chr(92),chr(92)+chr(92)).replace("'",chr(92)+"'");

}


var values = "";
var value1="";
var value2="";
var campi= "( field1 , field2 )";
for (var i = 0; i < id_myarray.getRowsCount(); i++){
if (i>0)
values= values + " , ";
value1=escape_string(id_myarray.id_first[i].getValue());
value2=escape_string(id_myarray.id_second[i].getValue());
values = values + "( '"+ value1 +"'";
values = values + " , '"+ value2 +"')";
}
id_values.setValue(values);
id_into.setValue(campi);
true;


escape_string doesn't work at all, I don't know why.

I've put chr(92) because if I put

str.replace(/\//g,"\\\\").replace(/'/g,"\\'");

the error is this:

Token manager error: Invalid freemarker: : Lexical error at line 13, column 22. Encountered: "/" (47), after : "\"\rfunction escape_string (str) {\rreturn str.replace(/\\"
Bad Request

my variable values must be like this:

('my first nam\'e is', 'my \' first sur\'name'),(' my second name \\ is', ' my second surname is'), etc etc...

If I delete escape_string from the script and I pass the values without escaping all works and the insert succeeds (but I can't insert names with ' as, for example, o'Reilly :-) )

Can someone help me with this simple escape function made to avoid sql injection?
Many thanks for your help!!
--
*WARNING: our spam filters may occasionally eliminate legitimate e-mails
from clients. *
*If your e-mail contains important instructions, please ensure that we
acknowledge receipt of those instructions.*
..............................................................................................................................................................................................................
This e-mail is confidential and may contain attorney privileged information
intended for the addressee(s) only.
Questo e-mail è riservato e tutelato dal segreto professionale ed è rivolto
esclusivamente ai destinatari identificati.
Ce courriel est confidentiel, il est couvert par le secret professionnel et
entièrement réservé aux seuls destinataires identifiés.
Este e-mail es confidencial y está protegido por el secreto profesional y
dirigido exclusivamente a los destinatarios identificados.

[Pray Desai]

Hi,

Did you try escaping from freemarker using freemarker functions like using ?js_string

{

"protocol":"JDBC",
"data":{
    "DBType":"SQLSERVER_2005",
    "sqlUsername":"....",
    "sqlPassword":"....",

    "sqlStatement":"insert into test${into} values ${values?js_string}"
    }
}

Refer http://freemarker.org/docs/ref_builtins_string.html#ref_builtin_js_string

Regards,

Pray Desai,

Fujitsu RunMyProcess.

--
Fujitsu - RunMyProcess
---
You received this message because you are subscribed to the Google Groups "Fujitsu RunMyProcess Developer Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to supportforum...@runmyprocess.com.
To post to this group, send email to suppor...@runmyprocess.com.
Visit this group at http://groups.google.com/a/runmyprocess.com/group/supportforum/.
To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/3297380e-a4c8-4daa-b662-8680e70b0ca0%40runmyprocess.com.
For more options, visit https://groups.google.com/a/runmyprocess.com/d/optout.

[asav...@jacobacci.com]

Yes, I did.
It doesn't work for my needs as it returns
(\'value\'1\',\'value\'2\'),(\'value3\',\'value4\')
I must have a string like this:
('value\'1','value\'2'),('value3','value4') etc..
not all the ' have to be escape for the sql statement.
${values} has to be prepared before.
If it's possible I'd like to prepare the variable (and put it into a filed of a form) when the user clicks on the form button for the insert in the database.
I tryed also
"sqlStatement":"insert into test${into} values '${values?js_string}'"
but the problem is the same as above, the results will be:
'(value\'1,value\'2),(value3,value4)' and this is not what sql expect in order to insert multiple row with an unique statement.
Regards,
Anna

[Pray Desai]

Hi,

It seems you did not return escaped string in your js function escape_string()

I tried the following script and it showed expected result on JS tester console :

function escape_string(str) { 

str.replace(/'/g,"\\'");

return str;

} 

var values = ""; 

var value1=""; 

var value2=""; 

var campi= "( field1 , field2 )"; 

for (var i = 0; i < 1; i++){ 

if (i>0) 

        values= values + " , "; 

value1=escape_string("abc"); 

value2=escape_string("o'riels"); 

        values = values + "( '"+ value1 +"'"; 

        values = values + " , '"+ value2 +"')"; 

}

values; 

Another best practice is to use RMPApplication.set("variable","value") instead of id_widget.setValue("value");

Regards,

Pray Desai,

Fujitsu RunMyProcess.

To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/209dea58-eb89-4a5c-931a-ce8ab0fa8822%40runmyprocess.com.

[asav...@jacobacci.com]

function escape_string(str) {
.... other commands.....
// 92 \ 39 '
var re = new RegExp(String.fromCharCode(39), 'g');
str = str.replace(re,String.fromCharCode(39)+String.fromCharCode(39));// it works!!!
var re1 = new RegExp(String.fromCharCode(92), 'g');
str = str.replace(re1,String.fromCharCode(92)+String.fromCharCode(92));// it doesn't work!!!!

str="'"+str+"'";

return str;
}


Why the ' is ok and the \ no??????

The javascript is in the Pre-Launch script of a button.
Can you help me? Many thanks!

[Pankaj Kumar]

Hi,

Since backslash "\" is used in escape character sequence i.e "\u0009" and in regular expression also i.e. "/a\*/".

For matching the  "\" charactyer in string, plese use the [\b] reqular expression.

Thank you,

Pankaj Kumar

Fujitsu RunMyProcess Support

To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/18afbc18-5288-4a28-8d0f-671cd21b6f2c%40runmyprocess.com.