The point I locate is: Sequelize prevents SQL injection writing
Our project was built using egg.js framework and sequelize ORM(Object Relational Mapping) framework for data processing
The corresponding function of folder is very clear. Router/controller/service is mainly used below
Have fun using Sequelize (find the simplest interface with no important information)
Routing:
'use strict'
/ * * *@param {Egg.Application} app - egg application
*/
module.exports = ({ router, controller: { soFileManage: { soFileManage, }, }, }) = > {
// Upload the list of so files
router.get('/so_file/manage', soFileManage.soFileList)
}
Copy the codeController (The controller is responsible for parsing the user’s input, processing and returning the corresponding results, generally in this area to receive parameters, get the data to expose)
'use strict'
const baseController = require('.. /baseController')
const fs = require('fs')
class soFileManageController extends baseController {
/** * Upload so file list */
async soFileList() {
const { ctx } = this
const { limit, offset } = this.paginationDeal(ctx.request.query)
const { list, total } = await this.service.soFileManage.soFileManage.soFileList({ limit, offset })
this.success({ data: { list, total } })
}
}
module.exports = soFileManageController
Copy the codeService (Service is an abstraction layer used to encapsulate business logic in complex business scenarios. We mainly take data from the database, sort the corresponding data, sort the corresponding data, and query it.)
'use strict'
const Service = require('egg').Service
class soFileManageService extends Service {
/** * Upload so file list *@param { Object } Object The passed object *@param { String } Object. limit Specifies the number of rows to be returned *@param { String } Object. offset How many lines to ignore before returning a line */
async soFileList({ limit, offset }) {
const { rows, count } = await this.ctx.model. Hahaha table name. FindAndCountAll ({attributes: { exclude: [ 'updatedAt']},order: [[ 'created_at'.'DESC' ]],
offset,
limit,
})
return { list: rows, total: count }
}
}
module.exports = soFileManageService
Copy the code2. Encountered problems that the Sequelize framework could not solve (using SQL)
SQL injection and malicious login through SQL injection are recommended
For example, our business often involves summarizing, cross-library queries, so we must write our own SQL, because the framework seems to be unable to implement, how to use Sequelize to prevent SQL injection?
2.1 What is SQL Injection
SQL injection is refers to the legitimacy of the web application to user input data without judgment or filtering is lax, the attacker can be defined in advance in the web application at the end of the query statement to add extra SQL statements, the administrator unwittingly illegal operation, in order to realize deceived any query of the database server to perform unauthorized, Thus further get the corresponding data information
Although I also use SQL is not a lot, I use my own words to translate: Usually we write SQL nothing more than check data is select XXX from a table where a field = YYy so to check, understand SQL people actually know the basic usage, if I write in the input box search number + some SQL stitching, so can get meaning hackers want to get the data
So how do you solve this problem? I know of two kinds:
1. Perform input verification
We can certainly do basic validation, but it is impossible for every input field to do validation for every input symbol
2. Precompilation prevents SQL injection
Before SQL execution, the above SQL will be sent to the database for compilation; To execute, directly replace the placeholder “? “with the compiled SQL. That’s it. Since SQL injection only works with the compile process, this approach is a good way to avoid SQL injection problems
To explain in plain English: Before SQL execution, SQL database compiled, so there are some important input data in SQL, we first use a symbol takes him, waiting for the real execution, then the important real data sets in, even if I write all sorts of SQL in the input box, compile operation has completed, things are just as input data, won’t be recompiled .
2.2 Simulating SQL injection
I’m going to simulate it directly with a tool, but if you simulate it in a project, it’s too hard to fake data
1 I can find two data in table A
2 Suppose you want to get data with id 1, there seems to be no problem below, type 1
3. But when you add SQL. The value is 1 or ID = 2
Although the data is simulated by me, I naturally know that ID =1, ID =2, these are not problems, just remember that one problem is that I entered SQL, has maliciously tampered with the original SQL server written, and there are more powerful malicious tampering
Sequelize prevents SQL injection
Basic usage
The link is here: precompilation in egg.js prevents SQL injection
Here is an example of how sequelize ORM is implemented
The link is here: Precompilation in Sequelize prevents SQL injection
3.1 Before injection prevention (The instance)
controller
TimeBegin, timeEnd start time, and end time are entered in the front end, which means that the time parameters are not injected and the SQL will be modified
async userData() {
const { ctx } = this
const { timeBegin, timeEnd } = this.paginationDeal(ctx.request.query)
let time = ' '
if (timeBegin && timeEnd) {
time = `AND hx_user.created_at between '${timeBegin}' and '${timeEnd}'`
}
const list = await this.service.userManage.userInfoList.userData(time)
this.success({ data: {
list,
} })
}
Copy the codeService (the summary has been removed to show only queries)
async userData(time) {
return await this.app.model.query(
Select count(1) FROM user_id WHERE user_id = hx_user.is_virtual SELECT count(1) FROM user_id WHERE user_id = hx_user.is_virtual = 0${time}; `,
{ type: 'SELECT'})}Copy the codeYou can see above that timeBegin,timeEnd is only written directly
3.2 Anti-injection modified code (? Placeholder /:key placeholder)
controller
async userData() {
const { ctx } = this
const { timeBegin, timeEnd } = this.paginationDeal(ctx.request.query)
const list = await this.service.userManage.userInfoList.userData(timeBegin, timeEnd)
this.success({ data: {
list,
} })
}
Copy the codeMethod one, using? Instead of symbol placeholders, use arrays for matching
service
async userData(timeBegin, timeEnd) {
let timeSql = ' '
let params = []
if (timeBegin && timeEnd) {
timeSql = 'AND hx_user.created_at between ? AND ? '
params = [ timeBegin, timeEnd ]
}
return await this.app.model.query(
`select count(1) 'registerUser', User_id WHERE hx_user.is_virtual = 0 USER_id WHERE hx_user.is_virtual = 0 user_id WHERE hx_user.is_virtual = 0${timeSql}; `,
{ replacements: params, type: 'SELECT'})}Copy the codeMethod two :key placeholder, with the object to match
service
async userData(timeBegin, timeEnd) {
let timeSql = ' '
if (timeBegin && timeEnd) {
timeSql = 'AND hx_user.created_at between :timeBegin AND :timeEnd'
}
return await this.app.model.query(
Select count(1) FROM user_id WHERE user_id = hx_user.is_virtual SELECT count(1) FROM user_id WHERE user_id = hx_user.is_virtual = 0${timeSql}; `,
{ replacements: { timeBegin, timeEnd }, type: 'SELECT'})}Copy the code3.3 What are the similarities and differences between the two methods of matching
Like this is a fuzzy query
What did I use originally? Key placeholder. Why is that?
If it is? And the array way to carry placeholders, there are some disadvantages, I tested quite a few, summarized below
1. Many????? You didn't know that? Which is the corresponding
2. Array and? There's a strict order, and one in the middle and you have to check to see if it's in the right place
3. Suppose I have three? Must correspond to three values in an array. Is an array four? No, that is, the placeholder data to do the judgment, into the push to do the judgment, and :key, as long as you do not have an object name no placeholder, does not match the value of replacements on the line
To sum up,:key is more suitable for objects, but if only for a number of substitution, there is no judgment, array also ok
Problems encountered in use
Because get the field data link table and get the total number of data written separately, at that time in order to test only in the first add replacements, this later after adding in the total number there is nothing, the results have been error, error is not obvious, if there is a similar writing method, directly add two test bar ~