Nodejs uses mysql fuzzy query

With the vigorous development of the Internet, today's database applications are becoming more and more widespread. In the database field, MySQL is a very popular database product. At the same time, JavaScript is also a very popular language for development languages, and Node.js is a server-side development environment built on JavaScript. In the combination of Node.js and MySQL, it is very necessary to be able to perform fuzzy queries. Today, let’s learn more about nodejs’ implementation of mysql fuzzy query.

1. Install Node.js and MySQL
Before officially starting to use mysql fuzzy query in nodejs, we need to install Node.js and MySQL first. The installation steps will not be repeated here.

2. Connecting to MySQL database
To connect to MySQL database in Node.js, you need to use the third-party module mysql module. Therefore, you need to install the mysql module through npm. The installation method is: enter npm install mysql on the command line.

After installing the mysql module, we need to introduce the mysql module and create a connection. The specific code is as follows:

const mysql = require('mysql');

// 创建连接
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'mydb'
});

connection.connect();

3. Fuzzy query in Node.js
In Node.js, fuzzy query can be implemented using the LIKE operator of MySQL. The LIKE operator is used to search for a pattern in a column in the WHERE clause. The basic syntax is as follows:

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

In LIKE syntax, pattern is used to specify the string or character pattern to be searched. You can use % and _ wildcards in pattern to perform fuzzy search. The specific meanings are as follows:
%: represents 0 or more characters.
_: represents any single character.

Suppose we have a table students, which contains the student's name (name) and student number (sno). We now need to query the information of all students with the surname Zhang. The specific code is as follows:

const searchName = '张';
const sql = `SELECT * FROM students WHERE name LIKE '%${searchName}%'`;

connection.query(sql, (err, results, fields) => {
   if (err) {
       console.error(err);
       return;
   }
   console.log(results);
});

connection.end();

Through the above code, we can achieve a fuzzy query for students with the surname Zhang in the students table. Here, the % wildcard character means that no matter what character is followed by the surname Zhang, it can be matched, so the information of all students with the surname Zhang can be queried.

4. Avoid SQL injection
Although fuzzy query has been implemented in the above code, there is a very serious problem, that is SQL injection. In the previous code, we directly spliced ​​the search content entered by the user into the SQL statement, which would give the attacker the opportunity to inject malicious code. Therefore, we need to follow some rules to avoid this risk.

Generally speaking, we can avoid SQL injection by escaping strings. The specific code is as follows:

const searchName = mysql.escape(req.query.name);
const sql = `SELECT * FROM students WHERE name LIKE '%${searchName}%'`;

connection.query(sql, (err, results, fields) => {
   if (err) {
       console.err(err);
       return;
   }
   console.log(results);
});

connection.end();

In the above code, we escape the search content entered by the user through the mysql.escape() method. This way you can avoid SQL injection problems.

5. Summary
Using mysql for fuzzy query in nodejs is a very practical and necessary operation. With the mysql module encapsulated in Node.js, we can easily connect to the MySQL database and implement various query operations. When performing fuzzy queries, we need to pay attention to avoid the risk of SQL injection to ensure data security.

The above is the detailed content of Nodejs uses mysql fuzzy query. For more information, please follow other related articles on the PHP Chinese website!