Update more SQL queries

3 files changed, 127 insertions, 118 deletions

diff --git a/src/ejabberd_auth_odbc.erl b/src/ejabberd_auth_odbc.erl
index f38c8286c..18d062012 100644
--- a/src/ejabberd_auth_odbc.erl
+++ b/src/ejabberd_auth_odbc.erl
@@ -187,26 +187,23 @@ try_register(User, Server, Password) ->
        (LUser == <<>>) or (LServer == <<>>) ->
             {error, invalid_jid};
        true ->
-	  Username = ejabberd_odbc:escape(LUser),
             case is_scrammed() of
                 true ->
                     Scram = password_to_scram(Password),
                     case catch odbc_queries:add_user_scram(
                                  LServer,
-                                 Username,
-                                 ejabberd_odbc:escape(Scram#scram.storedkey),
-                                 ejabberd_odbc:escape(Scram#scram.serverkey),
-                                 ejabberd_odbc:escape(Scram#scram.salt),
-                                 integer_to_binary(Scram#scram.iterationcount)
+                                 LUser,
+                                 Scram#scram.storedkey,
+                                 Scram#scram.serverkey,
+                                 Scram#scram.salt,
+                                 Scram#scram.iterationcount
                                 ) of
                         {updated, 1} -> {atomic, ok};
                         _ -> {atomic, exists}
                     end;
                 false ->
-                    Pass = ejabberd_odbc:escape(Password),
-                    case catch odbc_queries:add_user(LServer, Username,
-                                                     Pass)
-                        of
+                    case catch odbc_queries:add_user(LServer, LUser,
+                                                     Password) of
                         {updated, 1} -> {atomic, ok};
                         _ -> {atomic, exists}
                     end
@@ -221,35 +218,51 @@ dirty_get_registered_users() ->
 		  Servers).
 
 get_vh_registered_users(Server) ->
-    LServer = jid:nameprep(Server),
-    case catch odbc_queries:list_users(LServer) of
-      {selected, [<<"username">>], Res} ->
-	  [{U, LServer} || [U] <- Res];
-      _ -> []
+    case jid:nameprep(Server) of
+        error -> [];
+        <<>> -> [];
+        LServer ->
+            case catch odbc_queries:list_users(LServer) of
+                {selected, Res} ->
+                    [{U, LServer} || {U} <- Res];
+                _ -> []
+            end
     end.
 
 get_vh_registered_users(Server, Opts) ->
-    LServer = jid:nameprep(Server),
-    case catch odbc_queries:list_users(LServer, Opts) of
-      {selected, [<<"username">>], Res} ->
-	  [{U, LServer} || [U] <- Res];
-      _ -> []
+    case jid:nameprep(Server) of
+        error -> [];
+        <<>> -> [];
+        LServer ->
+            case catch odbc_queries:list_users(LServer, Opts) of
+                {selected, Res} ->
+                    [{U, LServer} || {U} <- Res];
+                _ -> []
+            end
     end.
 
 get_vh_registered_users_number(Server) ->
-    LServer = jid:nameprep(Server),
-    case catch odbc_queries:users_number(LServer) of
-      {selected, [_], [[Res]]} ->
-	  jlib:binary_to_integer(Res);
-      _ -> 0
+    case jid:nameprep(Server) of
+        error -> 0;
+        <<>> -> 0;
+        LServer ->
+            case catch odbc_queries:users_number(LServer) of
+                {selected, [{Res}]} ->
+                    Res;
+                _ -> 0
+            end
     end.
 
 get_vh_registered_users_number(Server, Opts) ->
-    LServer = jid:nameprep(Server),
-    case catch odbc_queries:users_number(LServer, Opts) of
-      {selected, [_], [[Res]]} ->
-	  jlib:binary_to_integer(Res);
-      _Other -> 0
+    case jid:nameprep(Server) of
+        error -> 0;
+        <<>> -> 0;
+        LServer ->
+            case catch odbc_queries:users_number(LServer, Opts) of
+                {selected, [{Res}]} ->
+                    Res;
+                _Other -> 0
+            end
     end.
 
 get_password(User, Server) ->
@@ -323,12 +336,14 @@ is_user_exists(User, Server) ->
 %% @doc Remove user.
 %% Note: it may return ok even if there was some problem removing the user.
 remove_user(User, Server) ->
-    case jid:nodeprep(User) of
-      error -> error;
-      LUser ->
-	  Username = ejabberd_odbc:escape(LUser),
-	  LServer = jid:nameprep(Server),
-	  catch odbc_queries:del_user(LServer, Username),
+    LServer = jid:nameprep(Server),
+    LUser = jid:nodeprep(User),
+    if (LUser == error) or (LServer == error) ->
+            error;
+       (LUser == <<>>) or (LServer == <<>>) ->
+            error;
+       true ->
+	  catch odbc_queries:del_user(LServer, LUser),
 	  ok
     end.
 
@@ -351,16 +366,12 @@ remove_user(User, Server, Password) ->
                         false -> not_allowed
                     end;
                 false ->
-                    Username = ejabberd_odbc:escape(LUser),
-                    Pass = ejabberd_odbc:escape(Password),
                     F = fun () ->
                                 Result = odbc_queries:del_user_return_password(
-                                           LServer, Username, Pass),
+                                           LServer, LUser, Password),
                                 case Result of
-                                    {selected, [<<"password">>],
-                                     [[Password]]} -> ok;
-                                    {selected, [<<"password">>],
-                                     []} -> not_exists;
+                                    {selected, [{Password}]} -> ok;
+                                    {selected, []} -> not_exists;
                                     _ -> not_allowed
                                 end
                         end,
diff --git a/src/ejabberd_odbc.erl b/src/ejabberd_odbc.erl
index b7aff7b14..6f7ce4c8b 100644
--- a/src/ejabberd_odbc.erl
+++ b/src/ejabberd_odbc.erl
@@ -41,6 +41,7 @@
 	 sql_bloc/2,
 	 escape/1,
 	 escape_like/1,
+	 escape_like_arg/1,
 	 to_bool/1,
 	 sqlite_db/1,
 	 sqlite_file/1,
@@ -125,7 +126,7 @@ start_link(Host, StartInterval) ->
                             {error, binary()} |
                             {selected, [binary()],
                              [[binary()]]} |
-                            {selected, [any]}.
+                            {selected, [any()]}.
 
 -spec sql_query(binary(), sql_query()) -> sql_query_result().
 
@@ -199,6 +200,13 @@ escape_like($%) -> <<"\\%">>;
 escape_like($_) -> <<"\\_">>;
 escape_like(C) when is_integer(C), C >= 0, C =< 255 -> odbc_queries:escape(C).
 
+escape_like_arg(S) when is_binary(S) ->
+    << <<(escape_like_arg(C))/binary>> || <<C>> <= S >>;
+escape_like_arg($%) -> <<"\\%">>;
+escape_like_arg($_) -> <<"\\_">>;
+escape_like_arg($\\) -> <<"\\\\">>;
+escape_like_arg(C) when is_integer(C), C >= 0, C =< 255 -> <<C>>.
+
 to_bool(<<"t">>) -> true;
 to_bool(<<"true">>) -> true;
 to_bool(<<"1">>) -> true;
diff --git a/src/odbc_queries.erl b/src/odbc_queries.erl
index 73abc2a0a..44e2dbfb0 100644
--- a/src/odbc_queries.erl
+++ b/src/odbc_queries.erl
@@ -175,39 +175,39 @@ set_password_scram_t(LServer, LUser,
                   "iterationcount=%(IterationCount)d"])
       end).
 
-add_user(LServer, Username, Pass) ->
-    ejabberd_odbc:sql_query(LServer,
-			    [<<"insert into users(username, password) "
-			       "values ('">>,
-			     Username, <<"', '">>, Pass, <<"');">>]).
+add_user(LServer, LUser, Password) ->
+    ejabberd_odbc:sql_query(
+      LServer,
+      ?SQL("insert into users(username, password) "
+           "values (%(LUser)s, %(Password)s)")).
 
-add_user_scram(LServer, Username,
+add_user_scram(LServer, LUser,
                StoredKey, ServerKey, Salt, IterationCount) ->
-    ejabberd_odbc:sql_query(LServer,
-			    [<<"insert into users(username, password, serverkey, salt, iterationcount) "
-			       "values ('">>,
-			     Username, <<"', '">>, StoredKey, <<"', '">>,
-                             ServerKey, <<"', '">>,
-                             Salt, <<"', '">>,
-                             IterationCount, <<"');">>]).
-
-del_user(LServer, Username) ->
-    ejabberd_odbc:sql_query(LServer,
-			    [<<"delete from users where username='">>, Username,
-			     <<"';">>]).
+    ejabberd_odbc:sql_query(
+      LServer,
+      ?SQL("insert into users(username, password, serverkey, salt, "
+           "iterationcount) "
+           "values (%(LUser)s, %(StoredKey)s, %(ServerKey)s,"
+           " %(Salt)s, %(IterationCount)d)")).
 
-del_user_return_password(_LServer, Username, Pass) ->
+del_user(LServer, LUser) ->
+    ejabberd_odbc:sql_query(
+      LServer,
+      ?SQL("delete from users where username=%(LUser)s")).
+
+del_user_return_password(_LServer, LUser, Password) ->
     P =
-	ejabberd_odbc:sql_query_t([<<"select password from users where username='">>,
-				   Username, <<"';">>]),
-    ejabberd_odbc:sql_query_t([<<"delete from users where username='">>,
-			       Username, <<"' and password='">>, Pass,
-			       <<"';">>]),
+	ejabberd_odbc:sql_query_t(
+          ?SQL("select @(password)s from users where username=%(LUser)s")),
+    ejabberd_odbc:sql_query_t(
+      ?SQL("delete from users"
+           " where username=%(LUser)s and password=%(Password)s")),
     P.
 
 list_users(LServer) ->
-    ejabberd_odbc:sql_query(LServer,
-			    [<<"select username from users">>]).
+    ejabberd_odbc:sql_query(
+      LServer,
+      ?SQL("select @(username)s from users")).
 
 list_users(LServer, [{from, Start}, {to, End}])
     when is_integer(Start) and is_integer(End) ->
@@ -222,64 +222,54 @@ list_users(LServer,
 		{offset, Start - 1}]);
 list_users(LServer, [{limit, Limit}, {offset, Offset}])
     when is_integer(Limit) and is_integer(Offset) ->
-    ejabberd_odbc:sql_query(LServer,
-			    [list_to_binary(
-                               io_lib:format(
-                                 "select username from users " ++
-                                     "order by username " ++
-                                     "limit ~w offset ~w",
-                                 [Limit, Offset]))]);
+    ejabberd_odbc:sql_query(
+      LServer,
+      ?SQL("select @(username)s from users "
+           "order by username "
+           "limit %(Limit)d offset %(Offset)d"));
 list_users(LServer,
 	   [{prefix, Prefix}, {limit, Limit}, {offset, Offset}])
     when is_binary(Prefix) and is_integer(Limit) and
 	   is_integer(Offset) ->
-    ejabberd_odbc:sql_query(LServer,
-			    [list_to_binary(
-                               io_lib:format(
-                                 "select username from users " ++
-                                     "where username like '~s%' " ++
-                                     "order by username " ++
-                                     "limit ~w offset ~w ",
-                                 [Prefix, Limit, Offset]))]).
+    SPrefix = ejabberd_odbc:escape_like_arg(Prefix),
+    SPrefix2 = <<SPrefix/binary, $%>>,
+    ejabberd_odbc:sql_query(
+      LServer,
+      ?SQL("select @(username)s from users "
+           "where username like %(SPrefix2)s "
+           "order by username "
+           "limit %(Limit)d offset %(Offset)d")).
 
 users_number(LServer) ->
-    Type = ejabberd_config:get_option({odbc_type, LServer},
-                                      fun(pgsql) -> pgsql;
-                                         (mysql) -> mysql;
-                                         (sqlite) -> sqlite;
-                                         (odbc) -> odbc
-                                      end, odbc),
-    case Type of
-      pgsql ->
-	  case
-	    ejabberd_config:get_option(
-              {pgsql_users_number_estimate, LServer},
-              fun(V) when is_boolean(V) -> V end,
-              false)
-	      of
-	    true ->
-		ejabberd_odbc:sql_query(LServer,
-					[<<"select reltuples from pg_class where "
-                                           "oid = 'users'::regclass::oid">>]);
-	    _ ->
-		ejabberd_odbc:sql_query(LServer,
-					[<<"select count(*) from users">>])
+    ejabberd_odbc:sql_query(
+      LServer,
+      fun(pgsql, _) ->
+              case
+                  ejabberd_config:get_option(
+                    {pgsql_users_number_estimate, LServer},
+                    fun(V) when is_boolean(V) -> V end,
+                    false) of
+                  true ->
+                      ejabberd_odbc:sql_query_t(
+                        ?SQL("select @(reltuples :: bigint)d from pg_class"
+                             " where oid = 'users'::regclass::oid"));
+                  _ ->
+                      ejabberd_odbc:sql_query_t(
+                        ?SQL("select @(count(*))d from users"))
 	  end;
-      _ ->
-	  ejabberd_odbc:sql_query(LServer,
-				  [<<"select count(*) from users">>])
-    end.
+         (_Type, _) ->
+              ejabberd_odbc:sql_query_t(
+                ?SQL("select @(count(*))d from users"))
+      end).
 
 users_number(LServer, [{prefix, Prefix}])
     when is_binary(Prefix) ->
-    ejabberd_odbc:sql_query(LServer,
-			    [list_to_binary(
-                               io_lib:fwrite(
-                                 "select count(*) from users " ++
-                                     %% Warning: Escape prefix at higher level to prevent SQL
-                                     %%          injection.
-                                     "where username like '~s%'",
-                                 [Prefix]))]);
+    SPrefix = ejabberd_odbc:escape_like_arg(Prefix),
+    SPrefix2 = <<SPrefix/binary, $%>>,
+    ejabberd_odbc:sql_query(
+      LServer,
+      ?SQL("select @(count(*))d from users "
+           "where username like %(SPrefix2)s"));
 users_number(LServer, []) ->
     users_number(LServer).