0

I have a Typescript application that interacts with a Microsoft SQL Server database and I am using the node mssql package to interact with it (to be precise, I am using the connection pool from a version of that package that allows for multiple databases to be connected):

import * as sql from "@cityssm/mssql-multi-pool";
import mssql from 'mssql';

I have the following function that creates dynamic queries:

function assembleInOutQuery(from: string, to: string, accounts: string[]): string {
    const base = `--sql
    SELECT 
        beleg AS receiptId,
        "index",
        fhaben,
        text,
        konto AS account,
        amount AS value,
        date
    FROM dbo.Buchung 
    WHERE date >= '${from}' AND date <= '${to}' AND `
    let accString = `(konto = ${accounts.shift()}`
    for (const acc of accounts) {
        accString = accString + ` OR konto = ${acc}`
    }
    accString = accString + ")"
    return base + accString
}

This code produces valid queries as accounts always contains at least one value. However, when executed through the node mssql package it produces the wrong output as the first value in accounts is always ignored. Due to this problem, I had to change it to the following function in order to produce the expected behaviour, however this is not a very good solution as it only circumvents the problem instead of solving it:

function assembleInOutQuery(from: string, to: string, accounts: string[]): string {
    let query = `SELECT 
        beleg AS receiptId,
        "index",
        fhaben,
        "text",
        konto AS account,
        amount AS "value",
        date
    FROM dbo.Buchung 
    WHERE (date >= '${from}' AND date <= '${to}') AND (konto = '0'`
    for (const acc of accounts) {
        query = `${query} OR konto = '${acc}'`
    }
    query = `${query});`
    return query
}

I had to include the (konto = '0' part because this part of the query seems to be dropped. It does not matter whether I put this part of the condition first in the WHERE clause or after the date being checked, the first value of "konto" I constrain on, is always dropped. Does anyone have an explanation why this happens?

I was not able to reproduce this behaviour in Microsoft SQL Server Management Studio, as there the query is executed exactly the way it is written. I checked the output of the function and executed it in Microsoft SQL Server Management Studio where it ran without problems and generated the expected output. When executed inside the application using the mssql package, the (konto = '0' part needs to be added for the output to be as expected.

Could this be due to some internal SQL injection protection the prevents certain parts of the query from being executed?

If anyone has suggestions as to how I can improve the above mentioned approach for generating the dynamic queries in a better/safer way, I'd also greatly appreciate it.

Thank you

Biiblebrox
  • 1
  • 1
  • have you seen how the string looked like after that `assembleInOutQuery` ran? – Bagus Tesa Aug 07 '23 at 13:28
  • @BagusTesa I have checked the output of the function and also ran it in Microsoft SQL Server Managment Studio, where the original query ran exactly as expected. Only when executed through the mssql package, parts of the query seem to be ignored. – Biiblebrox Aug 07 '23 at 16:43
  • 1
    The reason you need the dummy `konto = '0'`, it's because the code appends `OR konto = '${acc}'` repeatedly. Without the dummy clause you'd end up with `(OR konto ='blah' OR )` which is invalid. This has nothing to do with SQL injection protection – Panagiotis Kanavos Aug 07 '23 at 16:57
  • @PanagiotisKanavos I edited the question to be more precise, because your answer does not seem to be the reason I am looking for – Biiblebrox Aug 07 '23 at 20:18
  • I suggest you split the issue and work out really whether it's the driver doing this or your dynamic query code (I strong suspect it's your dynamc query code). For example, capture the dynamically generated query. Now hard code and submit that through the driver and I bet it just runs exactly what you submit. – Nick.Mc Aug 08 '23 at 01:53
  • @Nick.McDermaid I verified the correctness of the dynamically generated queries multiple times, so I am confident the problem lies with the driver. I was wondering if anyone knew what exactly the issue is and why it appears – Biiblebrox Aug 13 '23 at 19:14
  • I'm not doubting your thorough testing but what you're proposing is that some middleware is carefully removing just one small piece of your query. Keep in mind that it appears no one else come across this before. Can I suggest that you change to using `IN()` rather than a big list of or's. The results of that change might tell you something. For example if the very first term of the IN is missing, it really does point at some kind of dynamic sql issue. If it's not missing it remains a mystery but the problem is solved. – Nick.Mc Aug 13 '23 at 23:09
  • Also it may help to post the "expected" query and they query that appears to be submitted to the DB. Taking the time to write out the problem often results in you solving your problem. – Nick.Mc Aug 13 '23 at 23:12

0 Answers0