sql-fmt

Base by sqlstring

Simple SQL escape and format for MySQL easy create Ojbect to SQL, support typescript.

• sql.escape: Use sqlstring escape SQL string
• sql.values: Format and escape JS ['dog', 20] to SQL ('dog', 20
• sql.insert: Format and escape JS { key : value } to SQL (key1, key2) VALUES (value1, value2)
• sql.where: Format and escape JS { key : value } to SQL (key1='value1' AND key2='value2')
• sql.set: Format and escape JS { key : value } to SQL key1='value1', key2='value2'

Install

1$ npm install sql-fmt

1const sql = require("sql-fmt");

Use

Auto Single word:

1console.log(sql`SELECT * FROM users WHERE name=${"hello"};`);
2//out: SELECT * FROM users WHERE name='hello'
3
4console.log(sql`SELECT * FROM users WHERE name=${"'hello' and 1=1"};`);
5//out: SELECT * FROM users WHERE name='\'hello\' and 1=1'

Ignore escape Single word, add #:

1console.log(sql`SELECT * FROM users WHERE name=#${"hello"};`);
2//out: SELECT * FROM users WHERE name='hello'

Auto WHERE:

1console.log(sql`SELECT * FROM users WHERE ${{ name: "hello", age: [20, 30] }}`);
2//out: SELECT * FROM users WHERE (name='hello' AND (age=20 OR age=30))

Auto INSTER:

1console.log(sql`INSERT INTO users ${{ dog: "ff", age: 20 }};`);
2//out: INSERT INTO users (dog,age) VALUES ('ff',20);

Auto UPDATE SET:

1console.log(
2  sql`UPDATE users SET ${{ dog: "ff", age: 20 }} where ${{
3    name: "apple",
4  }};`
5);
6//out: UPDATE users SET dog='ff',age=20 where (name='apple');

No escape insert string, but need alert to SQL injection:

use # start string, sql-fmt can remove # before insert.

1console.log(sql`SELECT * FROM users ${"#where name = '20' and 1=1"}`);
2//out: SELECT * FROM users where name = '20' and 1=1

API

Auto use sql template insert where\ set \ values \ escape in string template

sql.escape

1console.log(`SELECT * FROM users WHERE name=${sql.escape("hello")};`);
2//out: SELECT * FROM users WHERE name='hello';
3
4console.log(`SELECT * FROM users WHERE name=${sql.escape("'hello' OR 1=1")}`);
5//out: SELECT * FROM users WHERE name='\'hello\' OR 1=1';

sql.escapeId

1console.log(`SELECT * FROM users WHERE ${sql.escapeId("name")}='hello';`);
2//out: SELECT * FROM users WHERE `name`='hello';

sql.values

1console.log(`INSERT INTO users (dog, age) values (${sql.insert(['ff', 20]);`);
2//out: INSERT INTO users (dog, age) VALUES ('ff',20);

sql.insert

1console.log(`INSERT INTO users ${sql.insert({ dog: "ff", age: 20 })};`);
2//out: INSERT INTO users (dog,age) VALUES ('ff',20);
3
4console.log(
5  `INSERT INTO users ${sql.insert([
6    { dog: "aa", age: 10 },
7    { dog: "bb", age: 20 },
8  ])};`
9);
10//out: INSERT INTO users (dog,age) VALUES ('aa',10),('bb',20);

sql.where

1console.log(`SELECT * FROM users WHERE ${sql.where({ dog: "ff", age: 20 })};`);
2//out: SELECT * FROM users WHERE (dog='ff' AND age=20);
3
4console.log(
5  `SELECT * FROM users WHERE ${sql.where({ dog: ["aa", "bb"], age: 20 })};`
6);
7//out: SELECT * FROM users WHERE ((dog='aa' OR dog='bb') AND age=20);

sql.set

1console.log(
2  `UPDATE users SET ${sql.set({ dog: "ff", age: 20 })} where ${sql.where({
3    name: "apple",
4  })};`
5);
6//out: UPDATE users SET dog='ff',age=20 where (name='apple');