EncodeForSQL()
Encodes the given string for safe output in a query to reduce the risk of SQL Injection attacks.
Deprecated: this function is deprecated and not recommended. It still works, but escaping characters cannot protect a query as reliably as bound parameters do. Use query parameters instead, see <cfqueryparam> and the params argument of QueryExecute().
The underlying ESAPI library removed this function in version 2.7.0 due to security concerns. The Lucee ESAPI extension keeps its own implementation, so existing code is not affected, but new code should not use it.
Requires Extension: Guard extension
EncodeForSQL( string=string, dialect=string, canonicalize=boolean );
Returns: String
| Argument | Description | Default |
|---|---|---|
|
string
string,
required
|
edit
string to encode |
|
|
dialect
string,
required
|
edit
SQL dialect used to encode, possible values are: * db2 * mysql_ansi * mysql * oracle |
|
|
canonicalize
boolean,
optional
|
edit
If set to true, canonicalization happens before encoding. If set to false, the given input string will just be encoded. The default value for canonicalize is false. When this parameter is not specified, canonicalization will not happen. By default, when canonicalization is performed, both mixed and multiple encodings will be allowed. To use any other combinations you should canonicalize using canonicalize method and then do encoding. Introduced: 5.0.0.0 |
false |
Examples
edit// example of an url variable:
// http://some.example.domain/dogs.cfm?name=lassie
url.name="lassie";
SQLQuery="SELECT * FROM dogs WHERE name='#url.name#';";
dump( SQLQuery );
// example of an url sql injection:
// http://some.example.domain/dogs.cfm?name='%20or%20'1'='1
url.name= "' or '1'='1";
SQLQuery="SELECT * FROM dogs WHERE name='#url.name#';";
dump( SQLQuery );
// example of preventing sql injection with encodeForSQL in MySQL:
SQLQuery="SELECT * FROM dogs WHERE name='#encodeForSQL( url.name, 'mySql' )#';";
dump( SQLQuery );
Recommended: use query parameters instead
dogs = queryNew(
"name, owner",
"varchar, varchar",
[ [ "lassie", "timmy" ], [ "rex", "anna" ] ]
);
// an url sql injection attempt
url.name = "' or '1'='1";
// the value is sent as a bound parameter and is never part of the SQL string
result = queryExecute(
sql = "SELECT * FROM dogs WHERE name = :name",
params = {
name: { value: url.name, type: "varchar" }
},
options = { dbtype: "query" }
);
dump( result.recordCount ); // 0, the injection attempt matches nothing
See also
- Encode/Decode
- Queries / Database
- string.encodeForSQL()
- Search Issue Tracker open_in_new
- Search Lucee Test Cases open_in_new (good for further, detailed examples)