Query of Node. js database operation MySQL database (II)

Preface

We have learned several methods of connecting Nodejs to MySQL database in the last article "Node. js database operation connection MySQL database (1)". After the database is connected, we need to query the database. This article introduces the method of querying MySQL database under 1. Let's not say much below, let's take a look at the detailed introduction.

Query mode

In the last article, we used one of the most basic methods to query the database: connection.query(sqlString, callback) .

The first parameter is an SQL statement, which can be any database statement, and the second parameter is a callback function, through which the query results are returned.

connection.query(
 'select * from book where author = "xyf" and country = "china"',
 function(err, result) {
 console.log(result);
 }
);

This is the simplest way to query, but there are two problems, one is the need to splice strings, more cumbersome; The other one is vulnerable to sql injection attack, so we have the second query method.

Placeholder injection query

The second query method is in the form of placeholders connection.query(sqlString, values, callback) So that you don't need to splice disgusting strings.

connection.query(
 'select * from book where author = ? and country = ?',
 ['xyf', 'china'], 
 function(err, result) {
 console.log(result);
 }
);

Use object query mode

In the third query method, we combine the query statement and the query value into an object to query. Its form is this: connection.query(object, callback) .

connection.query(
 {
 sql: 'select * from book where author = ? and country = ?',
 values: ['xyf', 'china'], //  As an attribute of an object 
 timeout: 40000,
 },
 function(err, result) {
 console.log(result);
 }
);

Combined query mode

The second and third methods can be used together, and the query value is used as a parameter of the query method, not as a property in the object.

connection.query(
 {
 sql: 'select * from book where author = ? and country = ?',
 timeout: 40000,
 // ['corner', 'us'] //  If set at the same time, it will not take effect at this time 
 },
 ['xyf', 'china'], //  As query Functional 1 Parameters 
 function(err, result) {
 console.log(result);
 }
);

It should be noted that if we take the query value as both an attribute of the object and a parameter of the query function, the parameters in the function will override the attribute of the object, which means that only the values of the parameters will take effect at this time.

Resolve query values

One important principle when making database queries is never to trust user input. Why can't you trust the user's input? First, let's understand the SQL injection attack under 1.

SQL injection attack

The so-called SQL injection attack is to deceive the server to execute malicious SQL commands by inserting SQL commands into the query string submitted by Web forms or entering domain names or page requests. Because the author is not engaged in database work, nor is it a brick home in database, so only through a simple DEMO to demonstrate 1 SQL injection attack.

If the SQL statement we spliced is like this

var username = 'xyf';
var sql = 'select * from book where author = "'+username+'"';

Here, we expect the user to enter username as Jack or LiLi, but the user says I don't want it, so I will enter a string of malicious code:

var username = '"1 or 1=1';
var sql = 'select * from book where author = "'+username+'"';

Finally, the query statement we spliced becomes the following:

select * from book where author = "" or 1=1

If readers have a basic understanding of SQL statement, they will know that if this query statement is put into the database for query, all the user information will be found out, but this is not the result we want to see.

Avoid SQL injection attack

So how can we avoid SQL injection attack? mysql provides us with two methods. The first method is to parse the data input by users with escape () function every time, which is somewhat similar to preprocessing statements.

var authorname = 'user input';
connection.escape(authorname);
//  Or use mysql.escape(authorname);
connection.query(
 'select * from book where author = "'+authorname+'"',
 function(err, result) {
 console.log(result);
 }
);

The second method is to query through the above-mentioned placeholder injection query method. However, its internal implementation also parses the user input 1 times through the escape () method mentioned above. The second method is recommended to be simple and quick.

Multi-statement query

mysql also supports multi-statement queries, but they are disabled by default for some security reasons (officially explained as an SQL injection attack if values are not resolved correctly). Then let's open this Pandora's Box.

Turn this function on first when creating a database connection.

let connection = mysql.createConnection({
 //  Other configurations 
 multipleStatements: true,
});

Then we can use multi-statement query.

connection.query(
 {
 sql: `select * from book where username = ?;
  select * from book where username = ?;`,
 },
 ['ace','xyf'],
 function(err, rows, fields) {
 if (err) throw err;
 console.log('The solution is: ', rows);
 }
);

Query results

The results returned by the query statement are returned in the form of an array. If it is a single statement query, the array is a pure object array [obj1, obj2,...], and every object in the array is every row of data in the database, which is only returned in the form of an object. If no data is queried, the length of the array is 0.

However, if it is a multi-statement (m statements) query, although the return is also an array, there are n arrays nested in the array, and the value of n depends on the number of query statements m (that is, n=m).

Summarize

Because the official documents are fragmented, they are not in place. I hope you can correct me if there are problems. The above is the whole content of this article. I hope the content of this article can bring 1 certain help to everyone's study or work. If you have any questions, you can leave a message for communication.