CraZySacX / node-jdbc

JDBC Wrapper for node.js
140 stars 107 forks source link

Using preparedStatement.set methods #195

Closed ghysal closed 5 years ago

ghysal commented 5 years ago

Hello, I've been using the node-jdbc for a while now and I managed to do pretty everything I needed.

Now my goal is to protect my SQL calls from injections so I checked a bit about Prepared Statements and I think it's exactly what I need.

I switched from my old "conn.createStatement" to some "conn.prepareStatement" followed by methods to set the parameters in my SQL query as follows :

`conn = connObj.conn;

            let sql = "SELECT OCNNOM, (OICRSV*1000000 + OICRDV) AS DVALIDITE FROM OIPCOND conds " + 
                    "LEFT OUTER JOIN OIPCRCND badges ON (badges.OCNNUM = conds.OCNNUM) " +
                    "WHERE badges.OCNNUM = ? AND badges.OICRTY = 'MOBILE'"; 

            console.log(sql);

            conn.prepareStatement(sql, function (err, pStatement)
            {
                if(err)
                {
                    handleRetours(Error("Erreur interne du serveur"), null);
                }
                else
                {   
                    console.log(matricule); 
                    pStatement.setString(0, matricule, function(err)
                    {
                        if(err)
                        {
                            console.log(err);
                            handleRetours(err);
                        }`

(handleRetours just sends back the error message et closes the connection).

When doing this I get the following error message : { [Error: Error running instance method java.sql.SQLException: Descriptor index not valid. at com.ibm.as400.access.JDError.throwSQLException(JDError.java:325) at com.ibm.as400.access.AS400JDBCPreparedStatement.setValue(AS400JDBCPreparedStatement.java:1864) at com.ibm.as400.access.AS400JDBCPreparedStatement.setString(AS400JDBCPreparedStatement.java:1669) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) ] cause: nodeJava_java_sql_SQLException {} }

PS : I'm using the JDBC driver to connect on a IBM400 System.

ghysal commented 5 years ago

Parameters indexes start at 1 not 0.