bind-sql-string
Helpful functionality to convert a query string, that has name/value bindings, into a query string better suited for your individual database engine.
Table of Contents
History
This code was born out of ibm_db not supporting named value binding parameters. It seems to only support positional question mark bindings.
Examples
Example String and Binding Array
Replace all named bindings with question marks. Most database engines support binding variables by an array. Not all support named value bindings. Use this function with a database engine that supports linear variable bindings
; const sql = ` SELECT * FROM SomeTable st WHERE st.SomeNumberColumn > :someNumberValue AND st.SomeStringColumn = :someStringValue AND st.SomeDate BETWEEN :startDate AND :endDate AND st.SomeOtherColumn<:someNumberValue AND st.SomeInColumn IN (:someStringValue,'B','C') AND st.SomeOtherColumn = 2+:someStringValue AND st.SomeStringArg = 'keep this '':binding''' AND st.SomeStringColumn IN (:multipleValues)`; const bindings = someNumberValue: 1 someStringValue: "He's got value" startDate: Date endDate: Date multipleValues: "A" "B" "C"; const setup = ; // RESULT: {sql: string, parameters: any[]} sql: ` SELECT * FROM SomeTable st WHERE st.SomeNumberColumn > ? AND st.SomeStringColumn = ? AND st.SomeDate BETWEEN ? AND ? AND st.SomeOtherColumn<? AND st.SomeInColumn IN (?,'B','C') AND st.SomeOtherColumn = 2+? AND st.SomeStringArg = 'keep this '':binding''' AND st.SomeStringColumn IN (?,?,?) ` parameters: 1 // someNumberValue 'He\'s got value' // someStringValue 1579032023955 // startDate 1579032023955 // endDate 1 // someNumberValue 'He\'s got value' // someStringValue 'He\'s got value' // someStringValue 'A' 'B' 'C'
Example String Return
Replace all named bindings with inline values. If you cannot bind variables at all in your query, use this function to at least support the syntax of name/value binding
; const sql = ` SELECT * FROM SomeTable st WHERE st.SomeNumberColumn > :someNumberValue AND st.SomeStringColumn = :someStringValue AND st.SomeDate BETWEEN :startDate AND :endDate AND st.SomeOtherColumn < :someNumberValue AND st.SomeStringColumn IN (:multipleValues)`; const bindings = someNumberValue: 1 someStringValue: "He's got value" startDate: Date endDate: Date; const setup = ; // RESULT: string SELECT *FROM SomeTable stWHERE stSomeNumberColumn > 1AND stSomeStringColumn = 'He''s got value'AND stSomeDate BETWEEN 1576258452153 AND 1576258452153AND stSomeOtherColumn < 1 AND stSomeStringColumn
Auto String Binding
This library can hoist all query string variables (aka query string literals) into binded parameters
const setup = queryBind(sql, bindings, {autoBindStrings: true});
Benefits to Auto String Binding
- You can use this library with code that has ZERO query binding parameters and get:
- Instantly you will have full SQL injection protection
- Instantly your database will better be able to cache query execution plans better
- Only applies to sql queries with only string values that would change
- WARN: Since all strings are auto casted to binding parameters some wierd things could happen:
- Database engine and/or connectors could have problems with string lengths
- Database engine and/or connectors could have datatype casting issues
- Developers may not know of auto string hoisting and become confused during debugging