How to prevent SQL injection?

https://www.wikidata.org/wiki/Q506059

 

SQL injection is used by hacker to gain or modify a table in a database.

 

This is A VERY IMPORTANT problem is webite

 

Suppose you have this code in your page:

 

sql("SELECT * FROM sed_login_user WHERE username = '" .+ cgidata.username .+ "'");

 

If you call this page with thie URL, you could do anything to the database:

 

page.sn?username=laplante%27%3Bshow%20tables%3bshow%20tables%20like%20%27

 

To prevent this you could do:

 

sql("SELECT * FROM sed_login_user WHERE username = '" .+ esc(filter:"sql",cgidata.username) .+ "'");

 

or do this:

 

 sql("SELECT * FROM sed_login_user WHERE username = '?'", cgidata.username);

 

If you ever need to pass the table name as a parameter in a resource for which table to select from, you should query the INFORMATION_SCHEMA table to check if the table is valid in the current database. Same principle with fields.

https://dev.mysql.com/doc/refman/8.0/en/information-schema.html

 

config = config();
// Validate table name is valid
sql_rs = sql("
 SELECT 1  FROM information_schema.tables
 WHERE table_schema = '?' AND table_name = '?'
", config.dbname, cgidata.table);

 

If the result above returns a row, the table name is valid and safe to do

res = sql("SELECT * FROM `?`", cgidata.table);