Express篇-连接mysql

1. 创建数据库配置文件config/sqlconfig.js

const sqlconfig = {host: 'localhost',  // 连接地址user: 'root',    //用户名password: '****',  //密码port:  3306 ,   //端口号database: 'mysql01_dbbooks'   //数据库名
}
module.exports = sqlconfig

2. 封装数据库管理工具 utils/mysqlUtils.js

连接数据库的两种简单方式:

• 使用mysql.createConnection连接数据库

• 使用连接池 pool.createPool()

const mysql = require('mysql')
const moment = require('moment')
// 加密模块中的随机生成数
const { randomInt } = require('crypto')
const sqlconfig = require('../config/sqlconfig')// 方式1   使用mysql.createConnection连接数据库
/* let mySql = (req,res,sql)=>{// console.log(sql);//连接数据库let conn= mysql.createConnection(sqlconfig)// 执行SQL语句conn.query(sql, (err, result)=>{// 错误信息if(err){console.log(err);return}console.log(result);res.send({message: '操作成功!',result})})
} */// 方式2   使用连接池 pool.createPool()
let pool = mysql.createPool(sqlconfig)
// 执行数据库
const exec2 = (sql,values) => {return new Promise((resolve, reject) => {pool.getConnection((err, conn) => {if (err) {//连接错误reject(err)} else {//连接成功// sql语句中使用?占位符, values传递是个数组conn.query(sql,values, (err, data) => {if (err) {//操作失败reject(err)} else {resolve({code: 0,message: '操作成功!',data,})// resolve(data)}})}// 当连接不再使用时，用conn对象的release方法将其归还到连接池中conn.release()})})
}
const exec = (sql) => {return new Promise((resolve, reject) => {pool.getConnection((err, conn) => {if (err) {//连接错误reject(err)} else {//连接成功conn.query(sql, (err, data) => {if (err) {//操作失败reject(err)} else {resolve({code: 0,message: '操作成功!',data,})// resolve(data)}})}// 当连接不再使用时，用conn对象的release方法将其归还到连接池中conn.release()})})
}// 查询
const searchList = (req, res, table = 'books') => {// 根据输入的查询条件查找数据   若无参数则查询所有let sql = `select * from  ${table} where 1=1`let keys = Object.keys(req.query)let values = Object.values(req.query)let keyArr = []let valArr = []let str = ''let i = 0keys.forEach((item,index)=>{keyArr.push(item.trim())console.log(item.trim());valArr.push(`${values[index].trim()}`)str += ` and ${keyArr[i]} like '%${valArr[i]}%' `i++})sql += strexec(sql).then(result=>{res.send({code: 1,message: "查询成功!",result})})
}// 增加数据
// 封装对象的方法
const addObj = (obj,table) => {if(table === 'books'){let { isbn='',bookname,imgUrl='',publisher='',pubYear=null,pages=0,content='',price=0.0    } = obj;if (pubYear !== null) {pubYear = moment(pubYear,'YYYY-MM-DD').toDate();}// 随机生成 bid   randomInt(x)返回0~x的随机整数const bid = moment().get() + '_' + (randomInt(9999999) + 1) + '';return Array.of(bid,isbn,bookname,imgUrl,publisher,pubYear,pages,content,price);}if(table === 'users'){let { nickname='',password,phone,email,uImg=null    } = obj;// 随机生成 uidconst uid = moment().get() + '_' + (randomInt(9999999) + 1) + '';return Array.of(uid,nickname,password,phone,email,uImg);}
}const addList = (req, res,  table = 'books') => {let { data } = req.bodylet values = []// data是个数组if(Array.isArray(data)){values = data.map(obj => addObj(obj,table))} else {// 对象values= addObj(data,table);}let sql = ''if(table === 'books'){// sql语句中 ? 为占位符, 表该处有内容, 具体是什么,后边传值,   所传数据(数组中的元素)的个数必须要和占位符的个数一样, 要以数组的形式进行传递(不论有几个元素)sql = ` insert into books(bid,isbn,bookname,imgUrl,publisher,pubYear,pages,content,price) values (?) `}if(table === 'users'){sql = ` insert into users(uid,nickname,password,phone,email,uImg) values (?) `}console.log("values:",values);// [values] 因为values本身是个数组,如果直接传递, 可能会有多个元素,与?个数不对应,     [values]相当于定义数组, 故传递时,变成了二维数组exec2(sql, [values]).then(data => {       res.send({ code: 1, message: "添加成功!",data });});
}// 更新数据  根据id更新
const updateList = (req, res, id, table = 'books', str='bid') => {const keys = Object.keys(req.body)const val = Object.values(req.body)let sql = ''let items = []keys.forEach((item, index) => {items.push(`${item} = '${val[index]}'`)})sql = `update ${table} set  ${items} where ${str} = '${id}'`console.log(sql)// mySql(req, res, sql)exec(sql).then(result=>{res.send({code: 1,result})})
}// 删除
const deleteList = (req, res, table = 'books') => {const keys = Object.keys(req.body)const val = Object.values(req.body)console.log(keys);if (keys.length !== 0) {// 按理来说, 需要查询数据库中是否有该数据let sql = `delete from  ${table}  where 1=1`let str = ''keys.forEach((item, index) => {str += ` and ${item} like '%${val[index]}%' `})console.log(sql);// mySql(req, res, sql)// let searchSQL= `select * from  ${table} where 1=1 ${str}`// exec(searchSQL).then(result=>{// })sql +=  strexec(sql).then(result=>{res.send({code: 1,message: "删除成功!",result})})} else {res.send({message: '删除条件为空!',})}
}const login = (req,res)=>{// 根据 phone / email 作为账号来登录 输入内容不为空， 判断是Email还是phone   根据对应信息查询数据， 如果能查到则登录成功， 否则登陆失败                                           let paramsArr = Object.values(req.body)// 有数据let flag = 0if(paramsArr && paramsArr.length > 0){// 遍历数组，并去空paramsArr.forEach(item => {if(item.trim().length === 0){flag++}})// 账号 密码存在if(flag === 0 && req.body.password.length > 0){let username = paramsArr.toString().indexOf('@') > 0 ? 'email' : 'phone'let sql = `select * from users where `if(username === 'email'){const {password , email } = req.body// 邮箱登录sql += ` password = '${ password }' and email = '${email}' `console.log(sql);// 查询数据， 查到则登录成功exec(sql).then(result=>{console.log(result.data);if(result.data.length > 0){res.send({code: 1,message: '登录成功!',result})}else{res.send({code: 0,message: '登录失败!'})}})}if(username === 'phone'){const {password , phone } = req.body// 邮箱登录sql += ` password = '${ password }' and phone = '${phone}' `console.log(sql);// 查询数据， 查到则登录成功exec(sql).then(result=>{// console.log(result);if(result.data.length > 0){res.send({code: 1,message: '登录成功!',result})}else{res.send({code: 0,message: '登录失败!'})}})}}else{// 账号或密码为空res.send({code: -1,message : "账号或密码不能为空!"})}}else{res.send({code: -1,message : "账号和密码不能为空!"})}
}module.exports = {searchList,addList,updateList,deleteList,login,
}

3.routes/user.js

var express = require('express');
const {searchList,addList,updateList,deleteList,login}= require('../utils/mysqlUtils')var router = express.Router();router.get('/searchList', function(req, res, next) {searchList(req,res,'users')
})
router.post('/addList', function(req, res, next) {addList(req,res,'users')
})router.post('/updateList', (req,res,next)=>{const {uid} = req.bodyif(uid && uid.trim() != '') {updateList(req,res,req.body.uid,'users','uid')}else{res.send({message: 'uid不能为空！！！'})}
})router.delete('/delList', (req,res,next)=>{// const {bid} = req.body// if(bid && bid.trim() != '') {deleteList(req,res,'users')// }else{//     res.send({//         message: 'bid不能为空！！！'//     })// }
})router.post('/login',(req,res,next)=>{login(req,res)
})module.exports = router

4.定时任务

在实际开发项目中，会遇到很多定时任务的工作。比如：定时导出某些数据、定时发送消息或邮件给用户、定时备份什么类型的文件等等。在nodejs中使用 node-schedule 完成定时任务。

安装：npm install node-schedule --save-dev

使用：

const schedule = require('node-schedule');
const scheduleCronstyle = ()=>{//每分钟的第30秒定时执行一次:schedule.scheduleJob('30 * * * * *',()=>{console.log('scheduleCronstyle:' + new Date());});
}
res.send(xss("<script> while(true){alert('111')}</script>"));举个栗子：每天凌晨2点0分0秒备份日志
const fs = require('fs');
const path = require('path');
const moment = require('moment');
server.listen(port, () => {// 添加定时器schedule.scheduleJob('0 0 2 * * *', () => {// 使用pipe,streamconst source = path.resolve(__dirname, '../logs', 'access.log');const target = path.resolve(__dirname, '../logs/bak', 'access.log' + moment().format('YYYYMMDDHHmmss') + '.bak');const rs = fs.createReadStream(source);const ws = fs.createWriteStream(target);rs.pipe(ws);ws.on('close', () => {console.log("备份完成！");});});
});

5.MySQL安全问题

(1)sql注入：窃取数据

最原始，最简单的攻击手段。 攻击方式：输入一个sql片段（s0001' or s.sid like '%%），最终拼接成一段攻击代码。

// 关键点解释
const arr = [1,2,3];
const [a,b,c]=arr;// 数组的解构
console.log("a=",a,"b=",b,"c=",c);
const arr2 = [arr]; // 定义数组
console.log(arr2);
// 结果
// a= 1 b= 2 c= 3
// [ [ 1, 2, 3 ] ]
bookRouter.post('/modify', (req, res, next) => {
const { bookid, bookname, isbn, publishinghouse } = req.body;
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 (publishinghouse && publishinghouse.trim() !== '') {
sql += ` , b.publishinghouse=? `;
params.push(publishinghouse);
}
sql += ` WHERE b.bid = ? `;
// 有多个占位符,(UPDATE books b SET b.bid = b.bid , b.isbn=? WHERE b.bid = ? ),
// 在传参时,数据的个数必须要和占位符的个数一样，要以数组的形式进行传递
params.push(bookid);
console.log('paramsType:', Object.prototype.toString.call(params), params);
exec2(sql,params).then(data => {
res.send(JSON.stringify({ code: 0, data }));
});
});

预防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= ? `;
exec2(sql, [username,password]);

• 预防xss攻击（xss攻击：窃取前端的cookie)

• 攻击方式：在页面展示内容中夹入js代码，以获取网页信息

• 预防措施：转换生成js的特殊字符1、安装xss: npm install xss -S

2、使用 xss(content)；// content代表内容

res.send(xss("<script> while(true){alert('111')}</script>"));

源代码放在这里啦~~~,朋友们帮我提提下载量呗
https://download.csdn.net/download/qq_54379580/87442975