Create a server on Node.js-CodePudding

I create a server on Node.js. I use SQL server. I want to creata a sql query that find book by the given input. When I wrote my query error was:

name: 'ERROR', handlerName: 'onErrorMessage', number: 207, state: 1, class: 16, message: "Invalid column name '%@param4%'.", serverName: 'DESKTOP-PQSULQS\SQLEXPRESS', procName: '', lineNumber: 1

This is a searchMethod:

async function searchBook(input){
    let result = await sql.query `SELECT * FROM dbo.Books WHERE Title = ${input} OR Kind = ${input} OR Author = ${input} OR Title LIKE "%${input}%"` ;
    return result.recordset;
}

I try to resolve this problem. But I don't know why server throw this exception.Any advices?

CodePudding user response：

the main issue with shared code sample is each nvarchar column value should to be encased in a single quotation '' if you gone use template literal with static vales.

then the code will be as the following:

async function searchBook(input){
    let result = await sql.query(`SELECT * FROM dbo.Books WHERE Title = '${input}' OR Kind = '${input}' OR Author = '${input}' OR Title LIKE '%${input}%'`);
    return result.recordset;
}

but this code has sql injection vulnerability so to prevent it. use parameters to overcome this issue.

so your code will be as the following:

 async function searchBook(input){
    let request = new sql.Request();
    request.input('input', sql.NVarChar, input);
    let result = await request.query(`SELECT * FROM dbo.Books WHERE Title = @input OR Kind = @input OR Author = @input OR Title LIKE '%'   @input   '%'`);
    return result.recordset;
}

CodePudding user response：

I think the problem lies in the construction of the request.

The use of parameters should solve the problem.

async function searchBook(input){
    let result = await sql.query`SELECT * FROM dbo.Books WHERE Title = @title OR Kind = @kind OR Author = @author OR Title LIKE "%"   @title   "%"`,
    {
        params: {
            title: input,
            kind: input,
            author: input
        }
    };
    return result.recordset;
}