[SOLVED] SQL statements in javascript with variables that contains an apostrophe

SQL statements in javascript with variables that contains an apostrophe

I have a working code in Mirth Connect (used to parse and dispatch HL7 data) that inserts data via jdbc into a database.

Javascript is used to generate the appropriate SQL statement.

Everything is working fine except when the variables have special characters in it like an apostrophe '

It throws a "There is an error in the syntax of the query"

var result = dbConn.executeUpdate(
"INSERT INTO Table (Column1, Column2) VALUES " 
+ "('"+ $('variable1')+ "'" 
+ ",'" + $('variable2')+ "'"
);

For instance: OK if variable1 = "max" but NOT OK if variable1 = "m'ax"

Is there an universal way (or a best way) how to deal with all the forbidden characters and make that the SQL statement is fine in each situation ?

Thanks in advance for your suggestions

[UPDATE]

Thanks for your suggestion. I tried this code:

importPackage(java.sql);
importPackage(java.io);

// database connexion information

new com.filemaker.jdbc.Driver();
var dbConn = DriverManager.getConnection('jdbc:filemaker://localhost/MYDATABASE','login','pass');


var ps = dbConn.prepareStatement("insert into Ehealth_Incoming (ehealthin_document_id_root, ehealthin_document_code_displayName, ehealthin_document_title)  values (?,?,?) ");


// parameter 1

ps.setString(1,$('id_attRoot'));

// parameter 2

ps.setString(2,$('code_attDisplayname'));

// parameter 3

ps.setString(3,$('title'));

// do it

ps.executeUpdate();

// close

ps.close();
dbConn.close();

But I receive a "Invalid parameter binding(s)" error. Any idea what I did wrong ? Thanks.

[SOLVED]

There was a syntax code mistake. Everything is working ! Thanks

Solution

You should use PreparedStatement, write your query as

INSERT INTO Table (Column1,Column2) VALUES (?,?)

then use the setString() method to set your values to the prepared statement.