nodejs连接mysql
1、首先进行mysql中间件的安装
npm install mysql --save
2、使用
// <1>mysql的配置
const mysqlConfig = {
host:'127.0.0.1',
port:'3306',
user:'root',
password:'mysql',
database:'doubandb'
}
// <2>引用mysql
const mysql = require('mysql');
// <3> 进行连接
const connection = mysql.createConnection(mysqlConfig);
// <4>操作数据库
connection.query("select * from books", (err, result) => {
console.log(err);
if (!err) {
data = result;
res.json({ data });
}
});
3、进行工具的封装
<1>MySQL的配置信息
const MYSQL_CONFIG = {
host: '127.0.0.1',
port: '3306',
user: 'root',
password: 'mysql',
database: 'doubandb'
}
module.exports = MYSQL_CONFIG;
<2>封装连接工具
// 引用mysql
const mysql = require('mysql');
const { MYSQL_CONFIG } = require('../../config/dbconfig');
// 创建连接池
const pool = mysql.createPool(MYSQL_CONFIG);
// 执行sql的方法
const execMysql = sql => {
return new Promise((resolve, reject) => {
// 获取连接
pool.getConnection((err, connection) => {
if (err) {
// 有错误
reject(err);
} else {
connection.query(sql, (err, result) => {
if (!err) {
resolve(result);
} else {
reject(err);
}
});
}
// 当连接不再使用时,用connection对象的release方法将其归还到连接池中
connection.release();
});
});
}
const execMysql2 = (sql,params) => {
return new Promise((resolve, reject) => {
// 从池子中获取连接
pool.getConnection((err, connection) => {
if (err) {
// 错误信息
reject(err)
} else {
connection.query(sql,params,(err, data) => {
if (err) {
reject(err)
} else {
resolve(data)
}
})
}
});
})
}
module.exports = {execMysql,execMysql2}
<3>在使用的地方调用方法,传递sql
单个条件数据查询:
// 查询数据----单个数据查询
const search = (table, req, res) => {
const key = Object.keys(req);
if (key[0] === undefined) {
sqlStr = `select * from ${table}`;
}
const values = Object.values(req);
const values1 = values[0].trim()
sqlStr = ` select * from ${table} where ${key} like '%${values1}%' `;
console.log(sqlStr)
// 封装了方法并调用方法
execMysql(sqlStr).then(data => {
console.log(data)
res.send({
message: "搜索成功",
data
})
})
}
多个条件查询数据
// 查询数据----通过多条数据查询
const searchMany = (req, res) => {
// 从query中拿到数据
let sqlStr = `SELECT b.*,a.aname FROM books b,author a,author_books ab WHERE ab.aid = a.aid AND ab.bookid = b.bookid`
const { bookid, bookname, aname, publisher, isbn } = req;
// 创建一个空的对象
const params = [];
// 判断参数是否存在 -- 存在拼接字符串
if (bookid && bookid.trim() !== '') {
sqlStr += ` and b.bookid=? `
params.push(`${bookid}`)
}
if (bookname && bookname.trim() !== '') {
sqlStr += ` and b.bookname like ? `
params.push(`%${bookname}%`)
}
if (aname && aname.trim() !== '') {
sqlStr += ` and a.aname like ? `;
// '%${authorname}%
params.push(`%${authorname}%`);
}
if (publisher && publisher.trim() !== '') {
sqlStr += ` and b.publisher=? `
params.push(`${publisher}`)
}
if (isbn && isbn !== '') {
sqlStr += ` and b.isbn=? `
params.push(isbn);
}
execMysql2(sqlStr, params).then(data => {
res.json({
message: "查询成功",
data
})
})
}
插入数据:
// 封装数据的方法
const addObj = obj => {
let {
bookname, price = '', isbn = '', publishtime = null, publisher = '', producer = '', translator = '', page = 0, contentind = '', typeid = null
} = obj;
// 处理时间
if (publishtime !== null) {
publishtime = moment(publishtime, 'YYYY-MM-DD').toDate();
}
// 使bookid随机生成
const bookid = moment().get() + '_' + (Math.random() * 100000000 + 1) + '';
return Array.of(bookid, bookname, price, isbn, publishtime, publisher, producer, translator, page, contentind, typeid);
}
// 插入数据---插入多条数据
const inserts = (req, res) => {
// 向数据库中添加数据
let { data } = req; // data数组
let values = [];
// 如果data是数组
if (Array.isArray(data)) {
values = data.map(obj => addObj(obj))
} else {
// 对象
values = addObj(data);
}
const sql = `insert into books(bookid, bookname, price, isbn, publishtime,
publisher, producer, translator, page, contentind, typeid) values (?)`;
execMysql2(sql, [values]).then(data => {
res.json({
message: "数据插入成功",
data
});
});
}
tips:
将字符串转换成mysql中日期类型:DATE_FORMAT(‘2023-02-07’,‘%Y-%m-%d’)或STR_TO_DATE(‘2023-02-07’,‘%Y-%m-%d’)
格式参考:
[(51条消息) mysql中字符串和时间互相转换的方法(自动转换及DATE_FORMAT函数)_harryho的博客-CSDN博客](https://blog.csdn.net/harryho/article/details/77978424)
删除数据
// 删除数据------删除一条数据,不传参删除整个表的数据
const dele = (table, req, res) => {
let sqlStr = '';
const key = Object.keys(req)
if (key[0] === undefined) {
sqlStr = `truncate ${table}`;
} else {
const values = Object.values(req)
sqlStr = `delete from ${table} where ${key[0]}='${values[0]}'`;
}
console.log(sqlStr)
execMysql(sqlStr).then(data => {
res.json({
message: "删除数据成功",
data
})
})
}
删除多条数据(以bookid进行删除)
// 删除多表数据
const deles = (req, res) => {
const { bids } = req;// bids是数
if (bids && bids.length > 0) {
const sql = `delete from books b where b.bookid in (?)`;
// 对bids进行解构
execMysql2(sql, [bids]).then(data => {
res.json({
message: "删除成功",
data
});
});
} else {
res.json({
code: 0,
message: '必须要进行选择'
});
}
}
更新数据
// 更新数据
const updata = (req, res) => {
const { bookid, bookname, isbn, publishtime } = req;
let sql = ` UPDATE books b SET b.bid = b.bid `;
const params = [];
if (bookname && bookname.trim() !== '') {
sql += ` , b.bookname= ? `;
params.push(bookname);
}
if (isbn && isbn.trim() !== '') {
sql += `, b.isbn=? `;
params.push(isbn);
}
if (publishtime && publishtime.trim() !== '') {
sql += ` , b.publishtime=? `;
params.push(publishtime);
}
sql += ` WHERE b.bid = ? `;
params.push(bookid);
execMysql2(sql, params).then(data => {
res.send(JSON.stringify({ code: 0, data }));
});
}
mysql安全问题
一、sql注入:窃取数据
最原始,最简单的攻击手段
攻击方式:输入一个sql片段(s0001’or s.sid like '%%),最终拼接成一段攻击代码。
1、预防sql注入:
<1>使用mysql提供的escape函数
const escape = require(‘mysql’).escape;
const sql = select * from users u where u.username=? and u.password= ${escape(password)}
;
<2>使用占位符
const sql = ` select * from users u where u.username=? and u.password= ? `;
// execMysql2(sql,values)
execMysql2(sql, [username,password]);
2、预防xss攻击(xss攻击:窃取前端的cookie)
攻击方式:在页面展示内容中夹入js代码,以获取页面信息
预防措施:转换生成js的特殊字符
<1>安装xss:npm install xss -S
<2>使用xss(content); // content代表内容
res.send(xss("<script> while(true){alert('111')}</script>"));
g-dfO4blzM-1676209038195)]
1、预防sql注入:
<1>使用mysql提供的escape函数
const escape = require(‘mysql’).escape;
const sql = select * from users u where u.username=? and u.password= ${escape(password)}
;
<2>使用占位符
const sql = ` select * from users u where u.username=? and u.password= ? `;
// execMysql2(sql,values)
execMysql2(sql, [username,password]);
2、预防xss攻击(xss攻击:窃取前端的cookie)
攻击方式:在页面展示内容中夹入js代码,以获取页面信息
[外链图片转存中…(img-Yalpznd2-1676209038197)]
预防措施:转换生成js的特殊字符
[外链图片转存中…(img-kr46hqrG-1676209038197)]
<1>安装xss:npm install xss -S
<2>使用xss(content); // content代表内容
res.send(xss("<script> while(true){alert('111')}</script>"));