Erlang and SQL Injection Attacks [duplicate]

Question

I work with the Oracle and MySQL Databases on a daily basis, from erlang application middle-ware. In these middle-ware applications, i am running Nitrogen Web Framework and Yaws Web Server plus a couple of erlang applications like RabbitMQ. The applications run within a trusted intranet despite their being vulnerable to SQL injection.

Attacks such as documented here: http://sqlzoo.net/hack/ are very able to render these applications useless. Is there a library (Erlang) or techniques of how i can prevent these attacks ? I have known that in other languages like PHP, ensuring that all parameters are stringified by escaping any single-quotes that may be inserted by a user or attacker.

Speaking of escaping all single-quotes in a string parameter, i came up with a replace_all function but it doesnot seem to solve my needs, because i thought i would find a way of replacing all single-quotes (') with an escaped version of themselves (\').

replace(Char,With,String)-> replace(Char,With,String,[]).

replace(_,_,[],Done)-> lists:reverse(Done);
replace(Char,With,[Char|Rest],Buffer)->
    replace(Char,With,Rest,[With|Buffer]);
replace(Char,With,[Any|Rest],Buffer)->
    replace(Char,With,Rest,[Any|Buffer]).

Testing it on a few examples:

3> sql_protect:replace($m,$k,"muzaaya").
"kuzaaya"
4> sql_protect:replace($a,$u,"muzaaya").
"muzuuyu"
5>

But how do i use this function to escape all single quotes. To have say String = "' OR 1=1'", changed to: String = "\' OR 1=1\'".

Your help is much appreciated.

EDIT

---

What do you think about this: if i have a list of all ORACLE SQL KEYWORDS and SQL SERVER KEYWORDS and MYSQL KEYWORDS in one list and for each query parameter, i split it into words ( that is if it contains spaces), and then i check to ensure that none of these contained words is a reserved word, i would prevent many sql injection attacks.

There seems to be a better way.

Answer 1

You need to escape not only ' but special symbols like \, \0 and so on too. So the quote function would look like (took it from our mysql driver):

quote(String) when is_list(String) ->
    [39 | lists:reverse([39 | quote(String, [])])]; %% 39 is $'
quote(Bin) when is_binary(Bin) ->
    list_to_binary(quote(binary_to_list(Bin))).

quote([], Acc) ->
    Acc;
quote([0 | Rest], Acc) ->
    quote(Rest, [$0, $\\ | Acc]);
quote([10 | Rest], Acc) ->
    quote(Rest, [$n, $\\ | Acc]);
quote([13 | Rest], Acc) ->
    quote(Rest, [$r, $\\ | Acc]);
quote([$\\ | Rest], Acc) ->
    quote(Rest, [$\\ , $\\ | Acc]);
quote([39 | Rest], Acc) ->        %% 39 is $'
    quote(Rest, [39, $\\ | Acc]); %% 39 is $'
quote([34 | Rest], Acc) ->        %% 34 is $"
    quote(Rest, [34, $\\ | Acc]); %% 34 is $"
quote([26 | Rest], Acc) ->
    quote(Rest, [$Z, $\\ | Acc]);
quote([C | Rest], Acc) ->
    quote(Rest, [C | Acc]).

Answer 2

I don't know about the erlang libraries, but I would assume that you could create prepared statements which when executed automatically 'sanitizes' your strings. This is even the preferred method in the php-land, with PDO and mysqli. The escape_strings and mysql driver are AFAIK deprecated.