Dev Tips: Escaping Special Characters

web security escape special character rubyonrails nunjuck nodejs sql sql injection url encoding html encoding

just happened that there are several issues / discussion topics around escaping characters these few days, so wanna share and remind every junior developer about these.

1) escaping in SQL statement

BAD:
execute('SELECT * FROM "users" WHERE "id" = ' + userId)  

there are occasions that you combine user provided data into a SQL that query database records.
in the examples above, userId is provided by application user (e.g. filling a form or typing in browser address bar)
user can provide a string that can break / hijack your SQL

For example if user provided '1 OR 1 ---'
so the code will somehow be like this:

execute('SELECT * FROM "users" WHERE "id" = 1 OR 1 ---')  

the part WHERE "id" = 1 OR 1 means always true and the executing the statement returns every user in the database table

what is even worse is that they can append a DELETE FROM statement into it as well.

GOOD:
execute('SELECT * FROM "users" WHERE "id" = ? ', userId)  

in using the function provided by the framework(e.g. Rails Active Record), there are standard methods that takes care the input parameter sanitization for you.
they will escape all the illegal characters in the value provided and just treat that as a normal harmless string.

2) escaping in URL

BAD:
https://example.com/shops?name=H & M  

this is an URL page that searches for a shop with name H & M
however, ampersand (&) in the name is a special character in URI format.

For reference, the usage of ampersand is like this:

http://example.com/home?param_1=value_1&params_2=value_2  

so in the search url, the server actually interpret that as

param_1: name  
value_1: H  
param_2:  M  

which is functionally incorrect and user wont be able to find the shops they intended to.

GOOD:
https://example.com/shops?name=H%20%26%20M  

this version with lots of percentage in it is the appropriate one.
all special characters are replaced by their URL Encoding (Percent Encoding), and now web server backend can interpret this as user expected.

3) escaping in dynamic HTML

BAD:
<input name="description" value="{{ post.description }}">  

in nunjuck (one of old libraries we are using to generate HTML code in an legacy project)
double curly braces ({{ }}) means interpolation / substitution.
it puts the value into the HTML file placeholder

however, when description has double quote inside, it becomes invalid HTML

<input name="description" value="My Name is "Eddie Lau"">  
GOOD:
<input name="description" value="{{ post.description | escape }}">  

it becomes

actually in most of the web frontend view engine nowadays, these has been taken care of as well