newLISP handling MySQL Escape character

What is an escape character

The escape character for MySQL refers to special characters that need to be escaped, which appear in SQL statements, which can cause SQL syntax errors or SQL injection attacks if not transferred.

There are several main types that need to be escaped:

Like ' need to become '

Here is the SQL test:

Mysql> INSERT into nodes (name) VALUES (' Select A.dt, COUNT (*), COUNT (distinct A.UV) by (select DT, Case if P2 in (' 04 ', ' 06 '); Error 1064 (42000): You have a error in your SQL syntax; Check the manual, corresponds to your MySQL server version for the right syntax to use near ' ", '" ') ' at line 1

The reason for the error is that there is a ' symbol ' in the string value, for example: ' 04 '

The workaround is to precede the \

INSERT into nodes ("name") VALUES (' Select A.dt, COUNT (*), COUNT (distinct A.UV) from (select DT, Case if P2 in (\ ' 04\ ', \ ' 0 6\ '); Query OK, 1 row affected (0.04 sec)

newLISP solution MySQL Client has provided a C function

Mysql_real_escape_string

newLISP's artful MySQL module has been used.

(Import libmysqlclient "mysql_real_escape_string")

Direct use: The Escape function, which uses the above mysql_real_escape_string function internally.

> (Load "/OPT/NEWLISP_UTIL/MYSQL.LSP") main> (setf db-src (MySQL)) (MySQL 27962464) > (: Escape db-src "SELECT DT, C ASE when p2 in (' ", '") "SELECT DT, Case when p2 in (\ \ ' 04\\ ', \ \ ' 06\\ ') \000     

This allows you to do a processing of the escape function once you run the stitched SQL statement:

You can also use: Advanced form of query:

;; (: Query db ' ("SELECT ID from Employees WHERE name =%s" ' ("Johnson, John"));; ; SQL generated:select ID from Employees WHERE name = ' Johnson, John ';; = (Mysqlresult 1069216)

This solves the problem of SQL statement formatting stitching and handling escape characters.

You only need to make a list of values and then pass them as the last argument to the query function.

newLISP handling MySQL escape character