Javascript SQLite for Cordova app - search using parameters where a string contains a substring case-insenstive

Question

In my Cordova app, I need to query a SQLite database and select rows where the value of the column EventName contains a substring. I want to be able to use ? to hold values to avoid SQL injection. I tried this query:

SELECT * FROM EventName WHERE 1 = 1 AND lower(EventName) LIKE lower('%?%');

This is my JavaScript code that I use to query the database:

function searchEvent(onSearch, eventName) {
    // First create the query string
    var params = [];
    var query = "SELECT * FROM Event WHERE 1 = 1";
    if (eventName != null && eventName != "") {
        query += " AND lower(EventName) LIKE lower('%?%')";
        params.push(eventName);
    }
    query += ";";
    console.log(query); // Log the query
    console.log(params); // Log the parameters
    // Then execute query statement
    db.transaction(function(tx) {
        tx.executeSql(query, params, function(tx, rs) {
            onSearch(rs);
        });
    }, function(err) {
        console.log(err); // This statement was executed
    });
}

This is the logged query:

SELECT * FROM Event WHERE 1 = 1 AND lower(EventName) LIKE lower('%?%');

This is the logged paramaters:

[ 'myInput' ]

This is the error the was returned:

{
    code: 5,
    messsage: 'number of \'?\'s in statement string does not match argument count'
}

As you can see there is 1 ? placeholder and 1 input parameter so the numbers DO match. I think it is because the ? is between the single quotes ('') so it is thought to be a part of the searched string. How do I fix this?

EDIT:

The JavaScript statement "SELECT * FROM Event WHERE 1 = 1" + " AND lower(EventName) LIKE lower('%" + eventName + "%')" is ok, but I wanna use a method that can protect me against SQL injection

Answer 1

In order to prevent the eventName from SQL injection, check it with regEx validation to include only alphanumneric and whitelist specific special characters /^[ A-Za-z0-9_@./#&+-]*$/. Also try this regEx /^[a-zA-Z0-9!@#\$%\^\&*)(+=._-]+$/g. I do not believe that ? would work with SQL SELECT statement, so you need to pass +eventname+

Hope it helps.