木子李

Appearance

sql语句 ​

sqlite ​

sqlite中的数据类型 ​

在SQLite中，数据类型相对灵活，主要支持以下几种数据类型：

  1. NULL：空值
  2. INTEGER：带符号整数，根据值的大小存储在1、2、3、4、6或8字节中
  3. REAL：浮点值，存储为8字节的IEEE浮点数
  4. TEXT：文本字符串，使用数据库编码（UTF-8、UTF-16BE或UTF-16LE）存储
  5. BLOB：二进制大对象，完全按照输入存储

使用typeorm ​

typeorm的基本使用 ​

ts
import 'reflect-metadata'
import { DataSource } from 'typeorm'
import { join } from 'path'
import { app } from 'electron'
import { User } from './tables'
// 数据库文件路径
export const AppDataSource = new DataSource({
  type: 'better-sqlite3',
  database: join(app.getPath('userData'), 'database.sqlite'),
  synchronize: true,
  logging: true,
  entities: [User],
  migrations: [],
  subscribers: []
})
try {
    await AppDataSource.initialize()
    console.log('数据库连接成功')
   } catch (error) {
    console.error('数据库连接失败:', error)
}

创建关联表设置外键 ​

sqlite
CREATE TABLE IF NOT EXISTS assets_list(id INTEGER PRIMARY KEY AUTOINCREMENT, task_id TEXT,file_name TEXT,file_path TEXT,img_info TEXT,is_recognition INTEGER,is_auth INTEGER DEFAULT 0,is_null INTEGER DEFAULT 0,is_sync INTEGER DEFAULT 0,sync_file TEXT,recognition_file TEXT,FOREIGN KEY (task_id ) REFERENCES tasks (id) ON DELETE CASCADE

上述sql使用typeorm:

ts
import { Entity, PrimaryGeneratedColumn, Column, ManyToOne } from 'typeorm'
import { Task } from './task.entity' // 假设已经存在Task实体

@Entity()
export class AssetsList {
  @PrimaryGeneratedColumn()
  id: number | undefined

  @Column({ type: 'text' })
  taskId: string | undefined

  @Column({ type: 'text' })
  fileName: string | undefined

  @Column({ type: 'text' })
  filePath: string | undefined

  @Column({ type: 'text' })
  imgInfo: string | undefined

  @Column({ type: 'integer' })
  isRecognition: number | undefined

  @Column({ type: 'integer', default: 0 })
  isAuth: number | undefined

  @Column({ type: 'integer', default: 0 })
  isNull: number | undefined

  @Column({ type: 'integer', default: 0 })
  isSync: number | undefined

  @Column({ type: 'text' })
  syncFile: string | undefined

  @Column({ type: 'text' })
  recognitionFile: string | undefined

  @ManyToOne(() => Task, task => task.assetsLists, { onDelete: 'CASCADE' })
  task: Task | undefined
}

创建索引 ​

sql
CREATE INDEX index_name ON table_name(column_name);

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER,
  email TEXT
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_email ON users(email，name);

index_name: 索引的名称，可以随意命名，但通常会包含表名和列名作为标识。

table_name: 需要创建索引的表名。

column_name: 需要创建索引的列名。

通过用户id获取关联表的所有数据 ​

sql
SELECT
  users.*,
  tasks.*,
  assets_list.*,
  recognition_result.*
FROM
  users
  JOIN tasks ON users.id = tasks.user_id
  JOIN assets_list ON tasks.id = assets_list.assets_id
  JOIN recognition_result ON assets_list.id = recognition_result.recognition_id
WHERE
  users.id = ?;

node中使用

js
const findTasks = DB.prepare(`
    SELECT
      users.*,
      tasks.*,
      assets_list.*,
      recognition_result.*
    FROM
      users
      JOIN tasks ON users.id = tasks.user_id
      JOIN assets_list ON tasks.id = assets_list.assets_id
    WHERE
      users.id = ?;
`);
findTasks.all(id)

动态生成UPDATE语句 ​

js
/**
 * @description 动态生成UPDATE语句
 * @param {string} table 表名
 * @param {object} data 数据不要包含id
 * @param {string} data.id 数据id
 */
function generateUpdateQuery(table, data, id) {
  let values = convertToSnakeCase(data);
  const keys = Object.keys(values);
  const placeholders = keys.map((key) => `${key} = $${key}`).join(', ');
  // const values = keys.map((key) => data[key]);

  const sql = `UPDATE ${table} SET ${placeholders} WHERE id = $id`;
  values.id = id;

  return { sql, values };
}
/**
 * @decription 更新识别结果数据
 * @param {array} recognition 对应的图片资源id的识别结果
 */
export const updateRecognitionFn = DB.transaction((recognition) => {
  recognition.forEach((i) => {
    let { id, ...data } = i;
    let { sql, values } = generateUpdateQuery('recognition_result', data, id);
    values.point = JSON.stringify(values.point);
    DB.prepare(sql).run(values);
  });
});

@和$占位符 ​

在SQL语句中，@id和$id是两种不同的参数占位符。

  • @id是一个命名参数占位符，通常在使用预编译语句时使用。在预编译语句中，你可以在语句中使用命名参数占位符，然后在运行语句时将参数值绑定到占位符上。例如，在你提供的SQL语句中，@id是一个命名参数占位符，它可以在运行语句时通过run方法的参数对象来绑定值。
  • $id是一个未命名参数占位符，通常在使用字符串拼接的方式构建SQL语句时使用。在这种情况下，你需要手动将参数值插入到SQL语句中，通常通过字符串拼接的方式来实现。

在你的代码中，@id是在预编译语句中使用的命名参数占位符，而$id是在字符串拼接的方式构建SQL语句中使用的未命名参数占位符。

总的来说，使用命名参数占位符可以提供更好的安全性和灵活性，因为它可以防止SQL注入攻击，并且可以在运行语句时动态绑定参数值。而使用未命名参数占位符则更适合于简单的字符串拼接的情况

sqlite成功插入数据后返回这条数据的id ​

DB.prepare('SELECT last_insert_rowid() AS id').get().id;

js
// 插入识别结果数据
const insertRecognition = DB.prepare(
  'INSERT INTO recognition_result (recognition_id, class_id, class_name, confidence, identify, is_edit, point) VALUES (@recognition_id, @class_id, @class_name, @confidence, @identify, @is_edit, @point)'
);

/**
 * @description 插入识别结果数据
 * @param {array} recognition 对应的图片资源id的识别结果
 * @returns {array} 插入数据的ID数组
 */
export const insertRecognitionFn = DB.transaction((recognition) => {
  let data = convertToSnakeCase(recognition);
  let insertedIds = [];
  
  data.forEach((i) => {
    insertRecognition.run({ ...i });
    // 获取刚插入数据的ID
    let insertedId = DB.prepare('SELECT last_insert_rowid() AS id').get().id;
    insertedIds.push(insertedId);
  });
  
  return insertedIds;
});

node环境中获取表的所有字段 ​

js
// 获取表的字段信息
function getTableColumns(tableName) {
  const stmt = DB.prepare(`PRAGMA table_info(${tableName})`);
  const columns = stmt.all();
  return columns;
}
const columns = getTableColumns('tasks');
console.log(columns);
/**[
  {
    cid: 0,
    name: 'id',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 1
  },
  {
    cid: 1,
    name: 'user_id',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 2,
    name: 'task_name',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 3,
    name: 'data_path',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 4,
    name: 'workspace',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 5,
    name: 'serial_number',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 6,
    name: 'camera_name',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 7,
    name: 'execute_type',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 8,
    name: 'install_time',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 9,
    name: 'install_place',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 10,
    name: 'getcard_time',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 11,
    name: 'remark',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 12,
    name: 'start_time',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 13,
    name: 'end_time',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 14,
    name: 'status',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 15,
    name: 'progress',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  },
  {
    cid: 16,
    name: 'task_type',
    type: 'TEXT',
    notnull: 0,
    dflt_value: null,
    pk: 0
  }

使用：

js
import { DB } from './sqlite.js';
/**
 * @description 将下划线分隔的字符串转换为小驼峰格式
 * @param {string} str
 */
export function toCamelCase(str) {
  return str.replace(/_([a-z])/g, (match, p1) => p1.toUpperCase());
}
/**
 * @description 递归地将对象或数组的键转换为小驼峰格式
 * @param {object | array} input
 */
export function convertToCamelCase(input) {
  if (Array.isArray(input)) {
    return input.map((item) => convertToCamelCase(item));
  } else if (input !== null && typeof input === 'object') {
    return Object.keys(input).reduce((acc, key) => {
      const camelCaseKey = toCamelCase(key);
      acc[camelCaseKey] = convertToCamelCase(input[key]);
      return acc;
    }, {});
  }
  return input;
}
/**
 * @description 递归地将对象或数组的键转换为下划线格式
 * @param {object | array} input
 */
export function convertToSnakeCase(input) {
  if (Array.isArray(input)) {
    return input.map((item) => convertToSnakeCase(item));
  } else if (input !== null && typeof input === 'object') {
    return Object.keys(input).reduce((acc, key) => {
      const camelCaseKey = toSnakeCase(key);
      acc[camelCaseKey] = convertToSnakeCase(input[key]);
      return acc;
    }, {});
  }
  return input;
}
/**
 * @description 动态生成UPDATE语句
 * @param {string} table 表名
 * @param {object} data 数据不要包含id
 * @param {string} data.id 数据id
 */
export function generateUpdateQuery(table, data, id) {
  let values = convertToSnakeCase(data);
  values = filterParamsByTableColumns(table, values);
  const keys = Object.keys(values);
  const placeholders = keys.map((key) => `${key} = $${key}`).join(', ');
  // const values = keys.map((key) => data[key]);

  const sql = `UPDATE ${table} SET ${placeholders} WHERE id = $id`;
  values.id = id;

  return { sql, values };
}
/**
 * @description 动态生成语INSERT句
 * @param {string} table 表名
 * @param {object} data 数据不要包含
 */
export function generateINSERTQuery(table, data) {
  let values = convertToSnakeCase(data);
  values = filterParamsByTableColumns(table, values);
  const keys = Object.keys(values);
  const placeholders = keys.join(', ');
  let value = keys.map((key) => `$${key}`).join(', ');
  // const values = keys.map((key) => data[key]);
  const sql = `INSERT INTO ${table} (${placeholders}) VALUES (${value})`;
  return { sql, values };
}
/**
 * @description -获取表的字段信息
 * @param {string} tableName -表名
 * @returns {array}
 */
// 获取表的字段信息
function getTableColumns(tableName) {
  const stmt = DB.prepare(`PRAGMA table_info(${tableName})`);
  const columns = stmt.all().map((column) => column.name);
  return columns;
}
/**
 * @description 过滤用户传入的参数，只保留与表字段匹配的部分
 * @param {string} tableName -表名
 * @param {object} userParams -用户传入参数
 * @returns {object}
 */
function filterParamsByTableColumns(tableName, userParams) {
  const tableColumns = getTableColumns(tableName);
  const filteredParams = {};

  for (const key in userParams) {
    if (tableColumns.includes(key)) {
      filteredParams[key] = userParams[key];
    }
  }

  return filteredParams;
}

万能密码 ​

sql
' OR '1'='1' -- '