Stronger tests in the test suite, SQL updates and fixes

11 files changed, 360 insertions, 348 deletions

diff --git a/src/ejabberd_sql.erl b/src/ejabberd_sql.erl
index f503a749f..a480a1bd3 100644
--- a/src/ejabberd_sql.erl
+++ b/src/ejabberd_sql.erl
@@ -41,6 +41,7 @@
 	 sql_bloc/2,
          sql_query_to_iolist/1,
 	 escape/1,
+         standard_escape/1,
 	 escape_like/1,
 	 escape_like_arg/1,
 	 escape_like_arg_circumflex/1,
@@ -216,6 +217,8 @@ escape_like_arg_circumflex(S) when is_binary(S) ->
 escape_like_arg_circumflex($%) -> <<"^%">>;
 escape_like_arg_circumflex($_) -> <<"^_">>;
 escape_like_arg_circumflex($^) -> <<"^^">>;
+escape_like_arg_circumflex($[) -> <<"^[">>;     % For MSSQL
+escape_like_arg_circumflex($]) -> <<"^]">>;
 escape_like_arg_circumflex(C) when is_integer(C), C >= 0, C =< 255 -> <<C>>.
 
 to_bool(<<"t">>) -> true;
diff --git a/src/mod_mam_sql.erl b/src/mod_mam_sql.erl
index d88806499..bbbe543f5 100644
--- a/src/mod_mam_sql.erl
+++ b/src/mod_mam_sql.erl
@@ -92,14 +92,16 @@ write_prefs(LUser, _LServer, #archive_prefs{default = Default,
 					   never = Never,
 					   always = Always},
 	    ServerHost) ->
-    SUser = ejabberd_sql:escape(LUser),
     SDefault = erlang:atom_to_binary(Default, utf8),
-    SAlways = ejabberd_sql:encode_term(Always),
-    SNever = ejabberd_sql:encode_term(Never),
-    case update(ServerHost, <<"archive_prefs">>,
-		[<<"username">>, <<"def">>, <<"always">>, <<"never">>],
-		[SUser, SDefault, SAlways, SNever],
-		[<<"username='">>, SUser, <<"'">>]) of
+    SAlways = jlib:term_to_expr(Always),
+    SNever = jlib:term_to_expr(Never),
+    case ?SQL_UPSERT(
+            ServerHost,
+            "archive_prefs",
+            ["!username=%(LUser)s",
+             "def=%(SDefault)s",
+             "always=%(SAlways)s",
+             "never=%(SNever)s"]) of
 	{updated, _} ->
 	    ok;
 	Err ->
@@ -109,10 +111,9 @@ write_prefs(LUser, _LServer, #archive_prefs{default = Default,
 get_prefs(LUser, LServer) ->
     case ejabberd_sql:sql_query(
 	   LServer,
-	   [<<"select def, always, never from archive_prefs ">>,
-	    <<"where username='">>,
-	    ejabberd_sql:escape(LUser), <<"';">>]) of
-	{selected, _, [[SDefault, SAlways, SNever]]} ->
+	   ?SQL("select @(def)s, @(always)s, @(never)s from archive_prefs"
+                " where username=%(LUser)s")) of
+	{selected, [{SDefault, SAlways, SNever}]} ->
 	    Default = erlang:binary_to_existing_atom(SDefault, utf8),
 	    Always = ejabberd_sql:decode_term(SAlways),
 	    Never = ejabberd_sql:decode_term(SNever),
@@ -211,6 +212,12 @@ make_sql_query(User, LServer, Start, End, With, RSM) ->
     ODBCType = ejabberd_config:get_option(
 		 {sql_type, LServer},
 		 ejabberd_sql:opt_type(sql_type)),
+    Escape =
+        case ODBCType of
+            mssql -> fun ejabberd_sql:standard_escape/1;
+            sqlite -> fun ejabberd_sql:standard_escape/1;
+            _ -> fun ejabberd_sql:escape/1
+        end,
     LimitClause = if is_integer(Max), Max >= 0, ODBCType /= mssql ->
 			  [<<" limit ">>, jlib:integer_to_binary(Max+1)];
 		     true ->
@@ -226,14 +233,14 @@ make_sql_query(User, LServer, Start, End, With, RSM) ->
 			 [];
 		     {text, Txt} ->
 			 [<<" and match (txt) against ('">>,
-			  ejabberd_sql:escape(Txt), <<"')">>];
+			  Escape(Txt), <<"')">>];
 		     {_, _, <<>>} ->
 			 [<<" and bare_peer='">>,
-			  ejabberd_sql:escape(jid:to_string(With)),
+			  Escape(jid:to_string(With)),
 			  <<"'">>];
 		     {_, _, _} ->
 			 [<<" and peer='">>,
-			  ejabberd_sql:escape(jid:to_string(With)),
+			  Escape(jid:to_string(With)),
 			  <<"'">>];
 		     none ->
 			 []
@@ -265,7 +272,7 @@ make_sql_query(User, LServer, Start, End, With, RSM) ->
 		    _ ->
 			[]
 		end,
-    SUser = ejabberd_sql:escape(User),
+    SUser = Escape(User),
 
     Query = [<<"SELECT ">>, TopClause, <<" timestamp, xml, peer, kind, nick"
 	      " FROM archive WHERE username='">>,
diff --git a/src/mod_offline_sql.erl b/src/mod_offline_sql.erl
index 4d9455570..a368e8e31 100644
--- a/src/mod_offline_sql.erl
+++ b/src/mod_offline_sql.erl
@@ -38,8 +38,7 @@ store_messages(Host, {User, _Server}, Msgs, Len, MaxOfflineMsgs) ->
        true ->
 	    Query = lists:map(
 		      fun(M) ->
-			      Username =
-				  ejabberd_sql:escape((M#offline_msg.to)#jid.luser),
+			      LUser = (M#offline_msg.to)#jid.luser,
 			      From = M#offline_msg.from,
 			      To = M#offline_msg.to,
 			      Packet =
@@ -49,9 +48,8 @@ store_messages(Host, {User, _Server}, Msgs, Len, MaxOfflineMsgs) ->
 				  jlib:add_delay_info(Packet, Host,
 						      M#offline_msg.timestamp,
 						      <<"Offline Storage">>),
-			      XML =
-				  ejabberd_sql:escape(fxml:element_to_binary(NewPacket)),
-				     sql_queries:add_spool_sql(Username, XML)
+			      XML = fxml:element_to_binary(NewPacket),
+                              sql_queries:add_spool_sql(LUser, XML)
 		      end,
 		      Msgs),
 	    sql_queries:add_spool(Host, Query)
@@ -95,19 +93,18 @@ remove_user(LUser, LServer) ->
     sql_queries:del_spool_msg(LServer, LUser).
 
 read_message_headers(LUser, LServer) ->
-    Username = ejabberd_sql:escape(LUser),
     case catch ejabberd_sql:sql_query(
-		 LServer, [<<"select xml, seq from spool where username ='">>,
-			   Username, <<"' order by seq;">>]) of
-	{selected, [<<"xml">>, <<"seq">>], Rows} ->
+		 LServer,
+                 ?SQL("select @(xml)s, @(seq)d from spool"
+                      " where username=%(LUser)s order by seq")) of
+	{selected, Rows} ->
 	    lists:flatmap(
-	      fun([XML, Seq]) ->
+	      fun({XML, Seq}) ->
 		      case xml_to_offline_msg(XML) of
 			  {ok, #offline_msg{from = From,
 					    to = To,
 					    packet = El}} ->
-			      Seq0 = binary_to_integer(Seq),
-			      [{Seq0, From, To, El}];
+			      [{Seq, From, To, El}];
 			  _ ->
 			      []
 		      end
@@ -117,13 +114,11 @@ read_message_headers(LUser, LServer) ->
     end.
 
 read_message(LUser, LServer, Seq) ->
-    Username = ejabberd_sql:escape(LUser),
-    SSeq = ejabberd_sql:escape(integer_to_binary(Seq)),
     case ejabberd_sql:sql_query(
 	   LServer,
-	   [<<"select xml from spool  where username='">>, Username,
-	    <<"'  and seq='">>, SSeq, <<"';">>]) of
-	{selected, [<<"xml">>], [[RawXML]|_]} ->
+	   ?SQL("select @(xml)s from spool where username=%(LUser)s"
+                " and seq=%(Seq)d")) of
+	{selected, [{RawXML}|_]} ->
 	    case xml_to_offline_msg(RawXML) of
 		{ok, Msg} ->
 		    {ok, Msg};
@@ -135,12 +130,10 @@ read_message(LUser, LServer, Seq) ->
     end.
 
 remove_message(LUser, LServer, Seq) ->
-    Username = ejabberd_sql:escape(LUser),
-    SSeq = ejabberd_sql:escape(integer_to_binary(Seq)),
     ejabberd_sql:sql_query(
       LServer,
-      [<<"delete from spool  where username='">>, Username,
-       <<"'  and seq='">>, SSeq, <<"';">>]),
+      ?SQL("delete from spool where username=%(LUser)s"
+           " and seq=%(Seq)d")),
     ok.
 
 read_all_messages(LUser, LServer) ->
@@ -180,14 +173,13 @@ export(_Server) ->
                              timestamp = TimeStamp, from = From, to = To,
                              packet = Packet})
             when LServer == Host ->
-              Username = ejabberd_sql:escape(LUser),
               Packet1 = jlib:replace_from_to(From, To, Packet),
               Packet2 = jlib:add_delay_info(Packet1, LServer, TimeStamp,
                                             <<"Offline Storage">>),
-              XML = ejabberd_sql:escape(fxml:element_to_binary(Packet2)),
-              [[<<"delete from spool where username='">>, Username, <<"';">>],
-               [<<"insert into spool(username, xml) values ('">>,
-                Username, <<"', '">>, XML, <<"');">>]];
+              XML = fxml:element_to_binary(Packet2),
+              [?SQL("delete from spool where username=%(LUser)s;"),
+               ?SQL("insert into spool(username, xml) values ("
+                    "%(LUser)s, %(XML)s);")];
          (_Host, _R) ->
               []
       end}].
diff --git a/src/node_flat_sql.erl b/src/node_flat_sql.erl
index 7cc2b6309..7b8ee552c 100644
--- a/src/node_flat_sql.erl
+++ b/src/node_flat_sql.erl
@@ -33,8 +33,11 @@
 -behaviour(gen_pubsub_node).
 -author('christophe.romain@process-one.net').
 
+-compile([{parse_transform, ejabberd_sql_pt}]).
+
 -include("pubsub.hrl").
 -include("jlib.hrl").
+-include("ejabberd_sql_pt.hrl").
 
 -export([init/3, terminate/2, options/0, features/0,
     create_node_permission/6, create_node/2, delete_node/1,
@@ -75,19 +78,25 @@ create_node_permission(Host, ServerHost, Node, ParentNode, Owner, Access) ->
 
 create_node(Nidx, Owner) ->
     {_U, _S, _R} = OwnerKey = jid:tolower(jid:remove_resource(Owner)),
-    State = #pubsub_state{stateid = {OwnerKey, Nidx}, affiliation = owner},
-    catch ejabberd_sql:sql_query_t([<<"insert into pubsub_state(nodeid, jid, affiliation, subscriptions) "
-		"values(">>, state_to_raw(Nidx, State), <<");">>]),
+    J = encode_jid(OwnerKey),
+    A = encode_affiliation(owner),
+    S = encode_subscriptions([]),
+    catch ejabberd_sql:sql_query_t(
+            ?SQL("insert into pubsub_state("
+                 "nodeid, jid, affiliation, subscriptions) "
+                 "values (%(Nidx)d, %(J)s, %(A)s, %(S)s)")),
     {result, {default, broadcast}}.
 
 delete_node(Nodes) ->
     Reply = lists:map(fun (#pubsub_node{id = Nidx} = PubsubNode) ->
 		    Subscriptions = case catch
-			ejabberd_sql:sql_query_t([<<"select jid, subscriptions "
-				    "from pubsub_state where nodeid='">>, Nidx, <<"';">>])
+			ejabberd_sql:sql_query_t(
+                          ?SQL("select @(jid)s, @(subscriptions)s "
+                               "from pubsub_state where nodeid=%(Nidx)d"))
 		    of
-			{selected, [<<"jid">>, <<"subscriptions">>], RItems} ->
-			    [{decode_jid(SJID), decode_subscriptions(Subs)} || [SJID, Subs] <- RItems];
+			{selected, RItems} ->
+			    [{decode_jid(SJID), decode_subscriptions(Subs)} ||
+                                {SJID, Subs} <- RItems];
 			_ ->
 			    []
 		    end,
@@ -299,13 +308,14 @@ get_entity_affiliations(Host, Owner) ->
     H = encode_host(Host),
     J = encode_jid(GenKey),
     Reply = case catch
-	ejabberd_sql:sql_query_t([<<"select node, type, i.nodeid, affiliation "
-		    "from pubsub_state i, pubsub_node n where "
-		    "i.nodeid = n.nodeid and jid='">>, J, <<"' and host='">>, H, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(node)s, @(type)s, @(i.nodeid)d, @(affiliation)s "
+               "from pubsub_state i, pubsub_node n where "
+               "i.nodeid = n.nodeid and jid=%(J)s and host=%(H)s"))
     of
-	{selected, [<<"node">>, <<"type">>, <<"nodeid">>, <<"affiliation">>], RItems} ->
-	    [{nodetree_tree_sql:raw_to_node(Host, [N, <<"">>, T, I]), decode_affiliation(A)}
-		|| [N, T, I, A] <- RItems];
+	{selected, RItems} ->
+	    [{nodetree_tree_sql:raw_to_node(Host, {N, <<"">>, T, I}), decode_affiliation(A)}
+		|| {N, T, I, A} <- RItems];
 	_ ->
 	    []
     end,
@@ -313,11 +323,12 @@ get_entity_affiliations(Host, Owner) ->
 
 get_node_affiliations(Nidx) ->
     Reply = case catch
-	ejabberd_sql:sql_query_t([<<"select jid, affiliation from pubsub_state "
-		    "where nodeid='">>, Nidx, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(jid)s, @(affiliation)s from pubsub_state "
+               "where nodeid=%(Nidx)d"))
     of
-	{selected, [<<"jid">>, <<"affiliation">>], RItems} ->
-	    [{decode_jid(J), decode_affiliation(A)} || [J, A] <- RItems];
+	{selected, RItems} ->
+	    [{decode_jid(J), decode_affiliation(A)} || {J, A} <- RItems];
 	_ ->
 	    []
     end,
@@ -328,10 +339,11 @@ get_affiliation(Nidx, Owner) ->
     GenKey = jid:remove_resource(SubKey),
     J = encode_jid(GenKey),
     Reply = case catch
-	ejabberd_sql:sql_query_t([<<"select affiliation from pubsub_state "
-		    "where nodeid='">>, Nidx, <<"' and jid='">>, J, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(affiliation)s from pubsub_state "
+               "where nodeid=%(Nidx)d and jid=%(J)s"))
     of
-	{selected, [<<"affiliation">>], [[A]]} ->
+	{selected, [{A}]} ->
 	    decode_affiliation(A);
 	_ ->
 	    none
@@ -353,23 +365,26 @@ get_entity_subscriptions(Host, Owner) ->
     H = encode_host(Host),
     SJ = encode_jid(SubKey),
     GJ = encode_jid(GenKey),
-    GJLike = encode_jid_like(GenKey),
-    Query = case SubKey of
-	GenKey ->
-	    [<<"select node, type, i.nodeid, jid, subscriptions "
-		    "from pubsub_state i, pubsub_node n "
-		    "where i.nodeid = n.nodeid and jid like '">>, GJLike,
-             <<"%' escape '^' and host='">>, H, <<"';">>];
-	_ ->
-	    [<<"select node, type, i.nodeid, jid, subscriptions "
-		    "from pubsub_state i, pubsub_node n "
-		    "where i.nodeid = n.nodeid and jid in ('">>, SJ, <<"', '">>, GJ, <<"') and host='">>, H, <<"';">>]
-    end,
+    GJLike = <<(encode_jid_like(GenKey))/binary, "%">>,
+    Query =
+        case SubKey of
+            GenKey ->
+                ?SQL("select @(node)s, @(type)s, @(i.nodeid)d,"
+                     " @(jid)s, @(subscriptions)s "
+                     "from pubsub_state i, pubsub_node n "
+                     "where i.nodeid = n.nodeid and jid like %(GJLike)s"
+                     " escape '^' and host=%(H)s");
+            _ ->
+                ?SQL("select @(node)s, @(type)s, @(i.nodeid)d,"
+                     " @(jid)s, @(subscriptions)s "
+                     "from pubsub_state i, pubsub_node n "
+                     "where i.nodeid = n.nodeid and jid in"
+                     " (%(SJ)s, %(GJ)s) and host=%(H)s")
+        end,
     Reply = case catch ejabberd_sql:sql_query_t(Query) of
-	{selected,
-		    [<<"node">>, <<"type">>, <<"nodeid">>, <<"jid">>, <<"subscriptions">>], RItems} ->
-	    lists:foldl(fun ([N, T, I, J, S], Acc) ->
-			Node = nodetree_tree_sql:raw_to_node(Host, [N, <<"">>, T, I]),
+	{selected, RItems} ->
+	    lists:foldl(fun ({N, T, I, J, S}, Acc) ->
+			Node = nodetree_tree_sql:raw_to_node(Host, {N, <<"">>, T, I}),
 			Jid = decode_jid(J),
 			case decode_subscriptions(S) of
 			    [] ->
@@ -404,25 +419,28 @@ get_entity_subscriptions_for_send_last(Host, Owner) ->
     H = encode_host(Host),
     SJ = encode_jid(SubKey),
     GJ = encode_jid(GenKey),
-    GJLike = encode_jid_like(GenKey),
-    Query = case SubKey of
-	GenKey ->
-	    [<<"select node, type, i.nodeid, jid, subscriptions "
-		    "from pubsub_state i, pubsub_node n, pubsub_node_option o "
-		    "where i.nodeid = n.nodeid and n.nodeid = o.nodeid and name='send_last_published_item' "
-		    "and val='on_sub_and_presence' and jid like '">>, GJLike,
-             <<"%' escape '^' and host='">>, H, <<"';">>];
-	_ ->
-	    [<<"select node, type, i.nodeid, jid, subscriptions "
-		    "from pubsub_state i, pubsub_node n, pubsub_node_option o "
-		    "where i.nodeid = n.nodeid and n.nodeid = o.nodeid and name='send_last_published_item' "
-		    "and val='on_sub_and_presence' and jid in ('">>, SJ, <<"', '">>, GJ, <<"') and host='">>, H, <<"';">>]
+    GJLike = <<(encode_jid_like(GenKey))/binary, "%">>,
+    Query =
+        case SubKey of
+            GenKey ->
+                ?SQL("select @(node)s, @(type)s, @(i.nodeid)d,"
+                     " @(jid)s, @(subscriptions)s "
+                     "from pubsub_state i, pubsub_node n, pubsub_node_option o "
+                     "where i.nodeid = n.nodeid and n.nodeid = o.nodeid and name='send_last_published_item' "
+                     "and val='on_sub_and_presence' and jid like %(GJLike)s"
+                     " escape '^' and host=%(H)s");
+            _ ->
+                ?SQL("select @(node)s, @(type)s, @(i.nodeid)d,"
+                     " @(jid)s, @(subscriptions)s "
+                     "from pubsub_state i, pubsub_node n, pubsub_node_option o "
+                     "where i.nodeid = n.nodeid and n.nodeid = o.nodeid and name='send_last_published_item' "
+                     "and val='on_sub_and_presence' and"
+                     " jid in (%(SJ)s, %(GJ)s) and host=%(H)s")
     end,
     Reply = case catch ejabberd_sql:sql_query_t(Query) of
-	{selected,
-		    [<<"node">>, <<"type">>, <<"nodeid">>, <<"jid">>, <<"subscriptions">>], RItems} ->
-	    lists:foldl(fun ([N, T, I, J, S], Acc) ->
-			Node = nodetree_tree_sql:raw_to_node(Host, [N, <<"">>, T, I]),
+	{selected, RItems} ->
+	    lists:foldl(fun ({N, T, I, J, S}, Acc) ->
+			Node = nodetree_tree_sql:raw_to_node(Host, {N, <<"">>, T, I}),
 			Jid = decode_jid(J),
 			case decode_subscriptions(S) of
 			    [] ->
@@ -442,11 +460,12 @@ get_entity_subscriptions_for_send_last(Host, Owner) ->
 
 get_node_subscriptions(Nidx) ->
     Reply = case catch
-	ejabberd_sql:sql_query_t([<<"select jid, subscriptions from pubsub_state "
-		    "where nodeid='">>, Nidx, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(jid)s, @(subscriptions)s from pubsub_state "
+               "where nodeid=%(Nidx)d"))
     of
-	{selected, [<<"jid">>, <<"subscriptions">>], RItems} ->
-	    lists:foldl(fun ([J, S], Acc) ->
+	{selected, RItems} ->
+	    lists:foldl(fun ({J, S}, Acc) ->
 			Jid = decode_jid(J),
 			case decode_subscriptions(S) of
 			    [] ->
@@ -468,10 +487,11 @@ get_subscriptions(Nidx, Owner) ->
     SubKey = jid:tolower(Owner),
     J = encode_jid(SubKey),
     Reply = case catch
-	ejabberd_sql:sql_query_t([<<"select subscriptions from pubsub_state where "
-		    "nodeid='">>, Nidx, <<"' and jid='">>, J, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(subscriptions)s from pubsub_state"
+               " where nodeid=%(Nidx)d and jid=%(J)s"))
     of
-	{selected, [<<"subscriptions">>], [[S]]} ->
+	{selected, [{S}]} ->
 	    decode_subscriptions(S);
 	_ ->
 	    []
@@ -568,13 +588,13 @@ get_nodes_helper(NodeTree, #pubsub_state{stateid = {_, N}, subscriptions = Subs}
 
 get_states(Nidx) ->
     case catch
-	ejabberd_sql:sql_query_t([<<"select jid, affiliation, subscriptions "
-		    "from pubsub_state where nodeid='">>, Nidx, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(jid)s, @(affiliation)s, @(subscriptions)s "
+               "from pubsub_state where nodeid=%(Nidx)d"))
     of
-	{selected,
-		    [<<"jid">>, <<"affiliation">>, <<"subscriptions">>], RItems} ->
+	{selected, RItems} ->
 	    {result,
-		lists:map(fun ([SJID, Aff, Subs]) ->
+		lists:map(fun ({SJID, Aff, Subs}) ->
                             JID = decode_jid(SJID),
                             #pubsub_state{stateid = {JID, Nidx},
 				items = itemids(Nidx, JID),
@@ -599,11 +619,12 @@ get_state(Nidx, JID) ->
 get_state_without_itemids(Nidx, JID) ->
     J = encode_jid(JID),
     case catch
-	ejabberd_sql:sql_query_t([<<"select jid, affiliation, subscriptions "
-		    "from pubsub_state where jid='">>, J, <<"' and nodeid='">>, Nidx, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(jid)s, @(affiliation)s, @(subscriptions)s "
+               "from pubsub_state "
+               "where nodeid=%(Nidx)d and jid=%(J)s"))
     of
-	{selected,
-		    [<<"jid">>, <<"affiliation">>, <<"subscriptions">>], [[SJID, Aff, Subs]]} ->
+	{selected, [{SJID, Aff, Subs}]} ->
 	    #pubsub_state{stateid = {decode_jid(SJID), Nidx},
 		affiliation = decode_affiliation(Aff),
 		subscriptions = decode_subscriptions(Subs)};
@@ -620,24 +641,20 @@ set_state(Nidx, State) ->
     J = encode_jid(JID),
     S = encode_subscriptions(State#pubsub_state.subscriptions),
     A = encode_affiliation(State#pubsub_state.affiliation),
-    case catch
-	ejabberd_sql:sql_query_t([<<"update pubsub_state set subscriptions='">>, S, <<"', affiliation='">>, A,
-		<<"' where nodeid='">>, Nidx, <<"' and jid='">>, J, <<"';">>])
-    of
-	{updated, 1} ->
-	    ok;
-	_ ->
-	    catch
-	    ejabberd_sql:sql_query_t([<<"insert into pubsub_state(nodeid, jid, affiliation, subscriptions) "
-			"values('">>,
-		    Nidx, <<"', '">>, J, <<"', '">>, A, <<"', '">>, S, <<"');">>])
-    end,
+    ?SQL_UPSERT_T(
+       "pubsub_state",
+       ["!nodeid=%(Nidx)d",
+        "!jid=%(J)s",
+        "affiliation=%(A)s",
+        "subscriptions=%(S)s"
+       ]),
     ok.
 
 del_state(Nidx, JID) ->
     J = encode_jid(JID),
-    catch ejabberd_sql:sql_query_t([<<"delete from pubsub_state where jid='">>,
-	    J, <<"' and nodeid='">>, Nidx, <<"';">>]),
+    catch ejabberd_sql:sql_query_t(
+            ?SQL("delete from pubsub_state"
+                 " where jid=%(J)s and nodeid=%(Nidx)d")),
     ok.
 
 %get_items(Nidx, _From) ->
@@ -655,12 +672,12 @@ del_state(Nidx, JID) ->
 
 get_items(Nidx, From, none) ->
     MaxItems = case catch
-	ejabberd_sql:sql_query_t([<<"select val from pubsub_node_option "
-		    "where nodeid='">>, Nidx, <<"' and name='max_items';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(val)s from pubsub_node_option "
+               "where nodeid=%(Nidx)d and name='max_items'"))
     of
-	{selected, [<<"val">>], [[Value]]} ->
-	    Tokens = element(2, erl_scan:string(binary_to_list(<<Value/binary, ".">>))),
-	    element(2, erl_parse:parse_term(Tokens));
+	{selected, [{Value}]} ->
+            jlib:expr_to_term(Value);
 	_ ->
 	    ?MAXITEMS
     end,
@@ -677,12 +694,13 @@ get_items(Nidx, _From,
 	_ ->
 	    {<<"is not">>, <<"desc">>}% Can be better
     end,
+    SNidx = integer_to_binary(Nidx),
     [AttrName, Id] = case I of
 	undefined when IncIndex =/= undefined ->
 	    case catch
 		ejabberd_sql:sql_query_t([<<"select modification from pubsub_item pi "
 			    "where exists ( select count(*) as count1 "
-			    "from pubsub_item where nodeid='">>, Nidx,
+			    "from pubsub_item where nodeid='">>, SNidx,
 			<<"' and modification > pi.modification having count1 = ">>,
 			ejabberd_sql:escape(jlib:i2l(IncIndex)), <<" );">>])
 	    of
@@ -700,7 +718,7 @@ get_items(Nidx, _From,
 	    [A, <<"'", B/binary, "'">>]
     end,
     Count = case catch
-	ejabberd_sql:sql_query_t([<<"select count(*) from pubsub_item where nodeid='">>, Nidx, <<"';">>])
+	ejabberd_sql:sql_query_t([<<"select count(*) from pubsub_item where nodeid='">>, SNidx, <<"';">>])
     of
 	{selected, [_], [[C]]} -> C;
 	_ -> <<"0">>
@@ -709,13 +727,13 @@ get_items(Nidx, _From,
 		    ejabberd_sql:sql_query_t(
 		      [<<"select top ">>, jlib:i2l(Max),
 		       <<" itemid, publisher, creation, modification, payload "
-			 "from pubsub_item where nodeid='">>, Nidx,
+			 "from pubsub_item where nodeid='">>, SNidx,
 		       <<"' and ">>, AttrName, <<" ">>, Way, <<" ">>, Id, <<" order by ">>,
 		       AttrName, <<" ">>, Order, <<";">>]);
 	       (_, _) ->
 		    ejabberd_sql:sql_query_t(
 		      [<<"select itemid, publisher, creation, modification, payload "
-			 "from pubsub_item where nodeid='">>, Nidx,
+			 "from pubsub_item where nodeid='">>, SNidx,
 		       <<"' and ">>, AttrName, <<" ">>, Way, <<" ">>, Id, <<" order by ">>,
 		       AttrName, <<" ">>, Order, <<" limit ">>, jlib:i2l(Max), <<" ;">>])
 	    end,
@@ -726,7 +744,7 @@ get_items(Nidx, _From,
 		[[_, _, _, F, _]|_] ->
 		    Index = case catch
 			ejabberd_sql:sql_query_t([<<"select count(*) from pubsub_item "
-				    "where nodeid='">>, Nidx, <<"' and ">>,
+				    "where nodeid='">>, SNidx, <<"' and ">>,
 				AttrName, <<" > '">>, F, <<"';">>])
 		    of
 			%{selected, [_], [{C}, {In}]} -> [string:strip(C, both, $"), string:strip(In, both, $")];
@@ -778,16 +796,17 @@ get_items(Nidx, JID, AccessModel, PresenceSubscription, RosterGroup, _SubId, RSM
 
 get_last_items(Nidx, _From, Count) ->
     Limit = jlib:i2l(Count),
+    SNidx = integer_to_binary(Nidx),
     Query = fun(mssql, _) ->
 		    ejabberd_sql:sql_query_t(
 		      [<<"select top ">>, Limit,
 		       <<" itemid, publisher, creation, modification, payload "
-			 "from pubsub_item where nodeid='">>, Nidx,
+			 "from pubsub_item where nodeid='">>, SNidx,
 		       <<"' order by modification desc ;">>]);
 	       (_, _) ->
 		    ejabberd_sql:sql_query_t(
 		      [<<"select itemid, publisher, creation, modification, payload "
-			 "from pubsub_item where nodeid='">>, Nidx,
+			 "from pubsub_item where nodeid='">>, SNidx,
 		       <<"' order by modification desc limit ">>, Limit, <<";">>])
 	    end,
     case catch ejabberd_sql:sql_query_t(Query) of
@@ -799,16 +818,14 @@ get_last_items(Nidx, _From, Count) ->
     end.
 
 get_item(Nidx, ItemId) ->
-    I = ejabberd_sql:escape(ItemId),
-    case catch
-	ejabberd_sql:sql_query_t([<<"select itemid, publisher, creation, "
-		    "modification, payload from pubsub_item "
-		    "where nodeid='">>, Nidx, <<"' and itemid='">>, I, <<"';">>])
+    case catch ejabberd_sql:sql_query_t(
+                 ?SQL("select @(itemid)s, @(publisher)s, @(creation)s,"
+                      " @(modification)s, @(payload)s from pubsub_item"
+                      " where nodeid=%(Nidx)d and itemid=%(ItemId)s"))
     of
-	{selected,
-		    [<<"itemid">>, <<"publisher">>, <<"creation">>, <<"modification">>, <<"payload">>], [RItem]} ->
+	{selected, [RItem]} ->
 	    {result, raw_to_item(Nidx, RItem)};
-	{selected, _, []} ->
+	{selected, []} ->
 	    {error, ?ERR_ITEM_NOT_FOUND};
 	{'EXIT', _} ->
 	    {error, ?ERRT_INTERNAL_SERVER_ERROR(?MYLANG, <<"Database failure">>)}
@@ -847,37 +864,31 @@ get_item(Nidx, ItemId, JID, AccessModel, PresenceSubscription, RosterGroup, _Sub
 
 set_item(Item) ->
     {ItemId, Nidx} = Item#pubsub_item.itemid,
-    I = ejabberd_sql:escape(ItemId),
     {C, _} = Item#pubsub_item.creation,
     {M, JID} = Item#pubsub_item.modification,
     P = encode_jid(JID),
     Payload = Item#pubsub_item.payload,
-    XML = ejabberd_sql:escape(str:join([fxml:element_to_binary(X) || X<-Payload], <<>>)),
+    XML = str:join([fxml:element_to_binary(X) || X<-Payload], <<>>),
     S = fun ({T1, T2, T3}) ->
 	    str:join([jlib:i2l(T1, 6), jlib:i2l(T2, 6), jlib:i2l(T3, 6)], <<":">>)
     end,
-    case catch
-	ejabberd_sql:sql_query_t([<<"update pubsub_item set publisher='">>, P,
-		<<"', modification='">>, S(M),
-		<<"', payload='">>, XML,
-		<<"' where nodeid='">>, Nidx, <<"' and itemid='">>, I, <<"';">>])
-    of
-	{updated, 1} ->
-	    ok;
-	_ ->
-	    catch
-	    ejabberd_sql:sql_query_t([<<"insert into pubsub_item (nodeid, itemid, "
-			"publisher, creation, modification, payload) "
-			"values('">>, Nidx, <<"', '">>, I, <<"', '">>, P,
-		    <<"', '">>, S(C), <<"', '">>, S(M),
-		    <<"', '">>, XML, <<"');">>])
-    end,
+    SM = S(M),
+    SC = S(C),
+    ?SQL_UPSERT_T(
+       "pubsub_item",
+       ["!nodeid=%(Nidx)d",
+        "!itemid=%(ItemId)s",
+        "publisher=%(P)s",
+        "modification=%(SM)s",
+        "payload=%(XML)s",
+        "-creation=%(SC)s"
+       ]),
     ok.
 
 del_item(Nidx, ItemId) ->
-    I = ejabberd_sql:escape(ItemId),
-    catch ejabberd_sql:sql_query_t([<<"delete from pubsub_item where itemid='">>,
-	    I, <<"' and nodeid='">>, Nidx, <<"';">>]).
+    catch ejabberd_sql:sql_query_t(
+            ?SQL("delete from pubsub_item where itemid=%(ItemId)s"
+                 " and nodeid=%(Nidx)d")).
 
 del_items(_, []) ->
     ok;
@@ -885,9 +896,10 @@ del_items(Nidx, [ItemId]) ->
     del_item(Nidx, ItemId);
 del_items(Nidx, ItemIds) ->
     I = str:join([[<<"'">>, ejabberd_sql:escape(X), <<"'">>] || X <- ItemIds], <<",">>),
+    SNidx = integer_to_binary(Nidx),
     catch
     ejabberd_sql:sql_query_t([<<"delete from pubsub_item where itemid in (">>,
-	    I, <<") and nodeid='">>, Nidx, <<"';">>]).
+	    I, <<") and nodeid='">>, SNidx, <<"';">>]).
 
 get_item_name(_Host, _Node, Id) ->
     Id.
@@ -908,14 +920,15 @@ first_in_list(Pred, [H | T]) ->
     end.
 
 itemids(Nidx, {_U, _S, _R} = JID) ->
-    SJID = encode_jid_like(JID),
+    SJID = <<(ejabberd_sql:escape(encode_jid_like(JID)))/binary, "%">>,
     case catch
-	ejabberd_sql:sql_query_t([<<"select itemid from pubsub_item where "
-		    "nodeid='">>, Nidx, <<"' and publisher like '">>, SJID,
-		<<"%' escape '^' order by modification desc;">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(itemid)s from pubsub_item where "
+               "nodeid=%(Nidx)d and publisher like %(SJID)s escape '^' "
+               "order by modification desc"))
     of
-	{selected, [<<"itemid">>], RItems} ->
-	    [ItemId || [ItemId] <- RItems];
+	{selected, RItems} ->
+	    [ItemId || {ItemId} <- RItems];
 	_ ->
 	    []
     end.
@@ -923,11 +936,11 @@ itemids(Nidx, {_U, _S, _R} = JID) ->
 select_affiliation_subscriptions(Nidx, JID) ->
     J = encode_jid(JID),
     case catch
-	ejabberd_sql:sql_query_t([<<"select affiliation,subscriptions from "
-		    "pubsub_state where nodeid='">>,
-		Nidx, <<"' and jid='">>, J, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(affiliation)s, @(subscriptions)s from "
+               " pubsub_state where nodeid=%(Nidx)d and jid=%(J)s"))
     of
-	{selected, [<<"affiliation">>, <<"subscriptions">>], [[A, S]]} ->
+	{selected, [{A, S}]} ->
 	    {decode_affiliation(A), decode_subscriptions(S)};
 	_ ->
 	    {none, []}
@@ -943,33 +956,24 @@ select_affiliation_subscriptions(Nidx, GenKey, SubKey) ->
 update_affiliation(Nidx, JID, Affiliation) ->
     J = encode_jid(JID),
     A = encode_affiliation(Affiliation),
-    case catch
-	ejabberd_sql:sql_query_t([<<"update pubsub_state set affiliation='">>,
-		A, <<"' where nodeid='">>, Nidx,
-		<<"' and jid='">>, J, <<"';">>])
-    of
-	{updated, 1} ->
-	    ok;
-	_ ->
-	    catch
-	    ejabberd_sql:sql_query_t([<<"insert into pubsub_state(nodeid, jid, affiliation, subscriptions) "
-			"values('">>, Nidx, <<"', '">>, J, <<"', '">>, A, <<"', '');">>])
-    end.
+    ?SQL_UPSERT_T(
+       "pubsub_state",
+       ["!nodeid=%(Nidx)d",
+        "!jid=%(J)s",
+        "affiliation=%(A)s",
+        "-subscriptions=''"
+       ]).
 
 update_subscription(Nidx, JID, Subscription) ->
     J = encode_jid(JID),
     S = encode_subscriptions(Subscription),
-    case catch
-	ejabberd_sql:sql_query_t([<<"update pubsub_state set subscriptions='">>, S,
-		<<"' where nodeid='">>, Nidx, <<"' and jid='">>, J, <<"';">>])
-    of
-	{updated, 1} ->
-	    ok;
-	_ ->
-	    catch
-	    ejabberd_sql:sql_query_t([<<"insert into pubsub_state(nodeid, jid, affiliation, subscriptions) "
-			"values('">>, Nidx, <<"', '">>, J, <<"', 'n', '">>, S, <<"');">>])
-    end.
+    ?SQL_UPSERT_T(
+       "pubsub_state",
+       ["!nodeid=%(Nidx)d",
+        "!jid=%(J)s",
+        "subscriptions=%(S)s",
+        "-affiliation='n'"
+       ]).
 
 -spec(decode_jid/1 ::
     (   SJID :: binary())
@@ -1016,24 +1020,24 @@ decode_subscriptions(Subscriptions) ->
     -> binary()
     ).
 encode_jid(JID) ->
-    ejabberd_sql:escape(jid:to_string(JID)).
+    jid:to_string(JID).
 
 -spec(encode_jid_like/1 :: (JID :: ljid()) -> binary()).
 encode_jid_like(JID) ->
-    ejabberd_sql:escape(ejabberd_sql:escape_like_arg_circumflex(jid:to_string(JID))).
+    ejabberd_sql:escape_like_arg_circumflex(jid:to_string(JID)).
 
 -spec(encode_host/1 ::
     (   Host :: host())
     -> binary()
     ).
 encode_host({_U, _S, _R} = LJID) -> encode_jid(LJID);
-encode_host(Host) -> ejabberd_sql:escape(Host).
+encode_host(Host) -> Host.
 
 -spec(encode_host_like/1 ::
     (   Host :: host())
     -> binary()
     ).
-encode_host_like({_U, _S, _R} = LJID) -> encode_jid_like(LJID);
+encode_host_like({_U, _S, _R} = LJID) -> ejabberd_sql:escape(encode_jid_like(LJID));
 encode_host_like(Host) ->
     ejabberd_sql:escape(ejabberd_sql:escape_like_arg_circumflex(Host)).
 
@@ -1069,12 +1073,14 @@ encode_subscriptions(Subscriptions) ->
 
 state_to_raw(Nidx, State) ->
     {JID, _} = State#pubsub_state.stateid,
-    J = encode_jid(JID),
+    J = ejabberd_sql:escape(encode_jid(JID)),
     A = encode_affiliation(State#pubsub_state.affiliation),
     S = encode_subscriptions(State#pubsub_state.subscriptions),
     [<<"'">>, Nidx, <<"', '">>, J, <<"', '">>, A, <<"', '">>, S, <<"'">>].
 
 raw_to_item(Nidx, [ItemId, SJID, Creation, Modification, XML]) ->
+    raw_to_item(Nidx, {ItemId, SJID, Creation, Modification, XML});
+raw_to_item(Nidx, {ItemId, SJID, Creation, Modification, XML}) ->
     JID = decode_jid(SJID),
     ToTime = fun (Str) ->
 	    [T1, T2, T3] = str:tokens(Str, <<":">>),
diff --git a/src/node_pep_sql.erl b/src/node_pep_sql.erl
index 62e9edc03..1df173fd7 100644
--- a/src/node_pep_sql.erl
+++ b/src/node_pep_sql.erl
@@ -116,9 +116,9 @@ get_entity_subscriptions(_Host, Owner) ->
     SubKey = jid:tolower(Owner),
     GenKey = jid:remove_resource(SubKey),
     HostLike = node_flat_sql:encode_host_like(element(2, SubKey)),
-    SJ = node_flat_sql:encode_jid(SubKey),
-    GJ = node_flat_sql:encode_jid(GenKey),
-    GJLike = node_flat_sql:encode_jid_like(GenKey),
+    SJ = ejabberd_sql:escape(node_flat_sql:encode_jid(SubKey)),
+    GJ = ejabberd_sql:escape(node_flat_sql:encode_jid(GenKey)),
+    GJLike = ejabberd_sql:escape(node_flat_sql:encode_jid_like(GenKey)),
     Query = case SubKey of
 	GenKey ->
 	    [<<"select host, node, type, i.nodeid, jid, "
@@ -152,9 +152,9 @@ get_entity_subscriptions_for_send_last(_Host, Owner) ->
     SubKey = jid:tolower(Owner),
     GenKey = jid:remove_resource(SubKey),
     HostLike = node_flat_sql:encode_host_like(element(2, SubKey)),
-    SJ = node_flat_sql:encode_jid(SubKey),
-    GJ = node_flat_sql:encode_jid(GenKey),
-    GJLike = node_flat_sql:encode_jid_like(GenKey),
+    SJ = ejabberd_sql:escape(node_flat_sql:encode_jid(SubKey)),
+    GJ = ejabberd_sql:escape(node_flat_sql:encode_jid(GenKey)),
+    GJLike = ejabberd_sql:escape(node_flat_sql:encode_jid_like(GenKey)),
     Query = case SubKey of
 	GenKey ->
 	    [<<"select host, node, type, i.nodeid, jid, "
diff --git a/src/nodetree_tree_sql.erl b/src/nodetree_tree_sql.erl
index 17ae91b52..1ad4046cd 100644
--- a/src/nodetree_tree_sql.erl
+++ b/src/nodetree_tree_sql.erl
@@ -37,8 +37,11 @@
 -behaviour(gen_pubsub_nodetree).
 -author('christophe.romain@process-one.net').
 
+-compile([{parse_transform, ejabberd_sql_pt}]).
+
 -include("pubsub.hrl").
 -include("jlib.hrl").
+-include("ejabberd_sql_pt.hrl").
 
 -export([init/3, terminate/2, options/0, set_node/1,
     get_node/3, get_node/2, get_node/1, get_nodes/2,
@@ -65,27 +68,27 @@ set_node(Record) when is_record(Record, pubsub_node) ->
     end,
     Type = Record#pubsub_node.type,
     H = node_flat_sql:encode_host(Host),
-    N = ejabberd_sql:escape(Node),
-    P = ejabberd_sql:escape(Parent),
     Nidx = case nodeidx(Host, Node) of
 	{result, OldNidx} ->
 	    catch
-	    ejabberd_sql:sql_query_t([<<"delete from pubsub_node_option where "
-			"nodeid='">>, OldNidx, <<"';">>]),
+	    ejabberd_sql:sql_query_t(
+              ?SQL("delete from pubsub_node_option where "
+                   "nodeid=%(OldNidx)d")),
 	    catch
-	    ejabberd_sql:sql_query_t([<<"update pubsub_node set host='">>,
-		    H, <<"' node='">>, N,
-		    <<"' parent='">>, P,
-		    <<"' type='">>, Type,
-		    <<"' where nodeid='">>,
-		    OldNidx, <<"';">>]),
+	    ejabberd_sql:sql_query_t(
+              ?SQL("update pubsub_node set"
+                   " host=%(H)s"
+                   " node=%(Node)s"
+                   " parent=%(Parent)s"
+                   " type=%(Type)s "
+                   "where nodeid=%(OldNidx)d")),
 	    OldNidx;
 	_ ->
 	    catch
-	    ejabberd_sql:sql_query_t([<<"insert into pubsub_node(host, node, "
-			"parent, type) values('">>,
-		    H, <<"', '">>, N, <<"', '">>, P,
-		    <<"', '">>, Type, <<"');">>]),
+	    ejabberd_sql:sql_query_t(
+              ?SQL("insert into pubsub_node(host, node, "
+                   "parent, type) values("
+                   "%(H)s, %(Node)s, %(Parent)s, %(Type)s)")),
 	    case nodeidx(Host, Node) of
 		{result, NewNidx} -> NewNidx;
 		_ -> none  % this should not happen
@@ -98,14 +101,12 @@ set_node(Record) when is_record(Record, pubsub_node) ->
 	_ ->
 	    lists:foreach(fun ({Key, Value}) ->
 			SKey = iolist_to_binary(atom_to_list(Key)),
-			SValue = ejabberd_sql:escape(
-				list_to_binary(
-				    lists:flatten(io_lib:fwrite("~p", [Value])))),
+			SValue = jlib:term_to_expr(Value),
 			catch
-			ejabberd_sql:sql_query_t([<<"insert into pubsub_node_option(nodeid, "
-				    "name, val) values('">>,
-				Nidx, <<"', '">>,
-				SKey, <<"', '">>, SValue, <<"');">>])
+			ejabberd_sql:sql_query_t(
+                          ?SQL("insert into pubsub_node_option(nodeid, "
+                               "name, val) values ("
+                               "%(Nidx)d, %(SKey)s, %(SValue)s)"))
 		end,
 		Record#pubsub_node.options),
 	    {result, Nidx}
@@ -116,14 +117,12 @@ get_node(Host, Node, _From) ->
 
 get_node(Host, Node) ->
     H = node_flat_sql:encode_host(Host),
-    N = ejabberd_sql:escape(Node),
     case catch
-	ejabberd_sql:sql_query_t([<<"select node, parent, type, nodeid from "
-		    "pubsub_node where host='">>,
-		H, <<"' and node='">>, N, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(node)s, @(parent)s, @(type)s, @(nodeid)d from "
+               "pubsub_node where host=%(H)s and node=%(Node)s"))
     of
-	{selected,
-		    [<<"node">>, <<"parent">>, <<"type">>, <<"nodeid">>], [RItem]} ->
+	{selected, [RItem]} ->
 	    raw_to_node(Host, RItem);
 	{'EXIT', _Reason} ->
 	    {error, ?ERRT_INTERNAL_SERVER_ERROR(?MYLANG, <<"Database failure">>)};
@@ -133,13 +132,12 @@ get_node(Host, Node) ->
 
 get_node(Nidx) ->
     case catch
-	ejabberd_sql:sql_query_t([<<"select host, node, parent, type from "
-		    "pubsub_node where nodeid='">>,
-		Nidx, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(host)s, @(node)s, @(parent)s, @(type)s from "
+               "pubsub_node where nodeid=%(Nidx)d"))
     of
-	{selected,
-		    [<<"host">>, <<"node">>, <<"parent">>, <<"type">>], [[Host, Node, Parent, Type]]} ->
-	    raw_to_node(Host, [Node, Parent, Type, Nidx]);
+	{selected, [{Host, Node, Parent, Type}]} ->
+	    raw_to_node(Host, {Node, Parent, Type, Nidx});
 	{'EXIT', _Reason} ->
 	    {error, ?ERRT_INTERNAL_SERVER_ERROR(?MYLANG, <<"Database failure">>)};
 	_ ->
@@ -152,11 +150,11 @@ get_nodes(Host, _From) ->
 get_nodes(Host) ->
     H = node_flat_sql:encode_host(Host),
     case catch
-	ejabberd_sql:sql_query_t([<<"select node, parent, type, nodeid from "
-		    "pubsub_node where host='">>, H, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(node)s, @(parent)s, @(type)s, @(nodeid)d from "
+               "pubsub_node where host=%(H)s"))
     of
-	{selected,
-		    [<<"node">>, <<"parent">>, <<"type">>, <<"nodeid">>], RItems} ->
+	{selected, RItems} ->
 	    [raw_to_node(Host, Item) || Item <- RItems];
 	_ ->
 	    []
@@ -178,14 +176,12 @@ get_subnodes(Host, Node, _From) ->
 
 get_subnodes(Host, Node) ->
     H = node_flat_sql:encode_host(Host),
-    N = ejabberd_sql:escape(Node),
     case catch
-	ejabberd_sql:sql_query_t([<<"select node, parent, type, nodeid from "
-		    "pubsub_node where host='">>,
-		H, <<"' and parent='">>, N, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(node)s, @(parent)s, @(type)s, @(nodeid)d from "
+               "pubsub_node where host=%(H)s and parent=%(Node)s"))
     of
-	{selected,
-		    [<<"node">>, <<"parent">>, <<"type">>, <<"nodeid">>], RItems} ->
+	{selected, RItems} ->
 	    [raw_to_node(Host, Item) || Item <- RItems];
 	_ ->
 	    []
@@ -196,14 +192,14 @@ get_subnodes_tree(Host, Node, _From) ->
 
 get_subnodes_tree(Host, Node) ->
     H = node_flat_sql:encode_host(Host),
-    N = ejabberd_sql:escape(ejabberd_sql:escape_like_arg_circumflex(Node)),
+    N = <<(ejabberd_sql:escape_like_arg_circumflex(Node))/binary, "%">>,
     case catch
-	ejabberd_sql:sql_query_t([<<"select node, parent, type, nodeid from "
-		    "pubsub_node where host='">>,
-		H, <<"' and node like '">>, N, <<"%' escape '^';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(node)s, @(parent)s, @(type)s, @(nodeid)d from "
+               "pubsub_node where host=%(H)s"
+               " and node like %(N)s escape '^'"))
     of
-	{selected,
-		    [<<"node">>, <<"parent">>, <<"type">>, <<"nodeid">>], RItems} ->
+	{selected, RItems} ->
 	    [raw_to_node(Host, Item) || Item <- RItems];
 	_ ->
 	    []
@@ -256,20 +252,24 @@ create_node(Host, Node, Type, Owner, Options, Parents) ->
 
 delete_node(Host, Node) ->
     H = node_flat_sql:encode_host(Host),
-    N = ejabberd_sql:escape(ejabberd_sql:escape_like_arg_circumflex(Node)),
+    N = <<(ejabberd_sql:escape_like_arg_circumflex(Node))/binary, "%">>,
     Removed = get_subnodes_tree(Host, Node),
-    catch ejabberd_sql:sql_query_t([<<"delete from pubsub_node where host='">>,
-	    H, <<"' and node like '">>, N, <<"%' escape '^';">>]),
+    catch ejabberd_sql:sql_query_t(
+            ?SQL("delete from pubsub_node where host=%(H)s"
+               " and node like %(N)s escape '^'")),
     Removed.
 
 %% helpers
 raw_to_node(Host, [Node, Parent, Type, Nidx]) ->
+    raw_to_node(Host, {Node, Parent, Type, binary_to_integer(Nidx)});
+raw_to_node(Host, {Node, Parent, Type, Nidx}) ->
     Options = case catch
-	ejabberd_sql:sql_query_t([<<"select name,val from pubsub_node_option "
-		    "where nodeid='">>, Nidx, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(name)s, @(val)s from pubsub_node_option "
+               "where nodeid=%(Nidx)d"))
     of
-	{selected, [<<"name">>, <<"val">>], ROptions} ->
-	    DbOpts = lists:map(fun ([Key, Value]) ->
+	{selected, ROptions} ->
+	    DbOpts = lists:map(fun ({Key, Value}) ->
 			    RKey = jlib:binary_to_atom(Key),
 			    Tokens = element(2, erl_scan:string(binary_to_list(<<Value/binary, ".">>))),
 			    RValue = element(2, erl_parse:parse_term(Tokens)),
@@ -295,13 +295,12 @@ raw_to_node(Host, [Node, Parent, Type, Nidx]) ->
 
 nodeidx(Host, Node) ->
     H = node_flat_sql:encode_host(Host),
-    N = ejabberd_sql:escape(Node),
     case catch
-	ejabberd_sql:sql_query_t([<<"select nodeid from pubsub_node where "
-		    "host='">>,
-		H, <<"' and node='">>, N, <<"';">>])
+	ejabberd_sql:sql_query_t(
+          ?SQL("select @(nodeid)d from pubsub_node where "
+               "host=%(H)s and node=%(Node)s"))
     of
-	{selected, [<<"nodeid">>], [[Nidx]]} ->
+	{selected, [{Nidx}]} ->
 	    {result, Nidx};
 	{'EXIT', _Reason} ->
 	    {error, db_fail};
diff --git a/src/sql_queries.erl b/src/sql_queries.erl
index 121117574..2afa32ac8 100644
--- a/src/sql_queries.erl
+++ b/src/sql_queries.erl
@@ -273,9 +273,8 @@ users_number(LServer, [{prefix, Prefix}])
 users_number(LServer, []) ->
     users_number(LServer).
 
-add_spool_sql(Username, XML) ->
-    [<<"insert into spool(username, xml) values ('">>,
-     Username, <<"', '">>, XML, <<"');">>].
+add_spool_sql(LUser, XML) ->
+    ?SQL("insert into spool(username, xml) values (%(LUser)s, %(XML)s)").
 
 add_spool(LServer, Queries) ->
     ejabberd_sql:sql_transaction(LServer, Queries).
diff --git a/test/ejabberd_SUITE.erl b/test/ejabberd_SUITE.erl
index 924b84e27..d3e7ec668 100644
--- a/test/ejabberd_SUITE.erl
+++ b/test/ejabberd_SUITE.erl
@@ -162,23 +162,23 @@ init_per_testcase(TestCase, OrigConfig) ->
     IsMaster = lists:suffix("_master", Test),
     IsSlave = lists:suffix("_slave", Test),
     IsCarbons = lists:prefix("carbons_", Test),
-    User = if IsMaster or IsCarbons -> <<"test_master">>;
-              IsSlave -> <<"test_slave">>;
-              true -> <<"test_single">>
+    User = if IsMaster or IsCarbons -> <<"test_master!#$%^*()`~+-;_=[]{}|\\">>;
+              IsSlave -> <<"test_slave!#$%^*()`~+-;_=[]{}|\\">>;
+              true -> <<"test_single!#$%^*()`~+-;_=[]{}|\\">>
            end,
     MyResource = if IsMaster and IsCarbons -> MasterResource;
 		    IsSlave and IsCarbons -> SlaveResource;
 		    true -> Resource
 		 end,
     Slave = if IsCarbons ->
-		    jid:make(<<"test_master">>, Server, SlaveResource);
+		    jid:make(<<"test_master!#$%^*()`~+-;_=[]{}|\\">>, Server, SlaveResource);
 	       true ->
-		    jid:make(<<"test_slave">>, Server, Resource)
+		    jid:make(<<"test_slave!#$%^*()`~+-;_=[]{}|\\">>, Server, Resource)
 	    end,
     Master = if IsCarbons ->
-		     jid:make(<<"test_master">>, Server, MasterResource);
+		     jid:make(<<"test_master!#$%^*()`~+-;_=[]{}|\\">>, Server, MasterResource);
 		true ->
-		     jid:make(<<"test_master">>, Server, Resource)
+		     jid:make(<<"test_master!#$%^*()`~+-;_=[]{}|\\">>, Server, Resource)
 	     end,
     Config = set_opt(user, User,
                      set_opt(slave, Slave,
@@ -882,7 +882,7 @@ pubsub(Config) ->
     true = lists:member(?NS_PUBSUB, Features),
     %% Publish <presence/> element within node "presence"
     ItemID = randoms:get_string(),
-    Node = <<"presence">>,
+    Node = <<"presence!@#$%^&*()'\"`~<>+-/;:_=[]{}|\\">>,
     Item = #pubsub_item{id = ItemID,
                         xml_els = [xmpp_codec:encode(#presence{})]},
     #iq{type = result,
diff --git a/test/ejabberd_SUITE_data/cert.pem b/test/ejabberd_SUITE_data/cert.pem
index d6429dd68..11e18491f 100644
--- a/test/ejabberd_SUITE_data/cert.pem
+++ b/test/ejabberd_SUITE_data/cert.pem
@@ -1,51 +1,52 @@
 -----BEGIN CERTIFICATE-----
-MIIDETCCAcmgAwIBAgIEUbsa1zANBgkqhkiG9w0BAQsFADAAMCIYDzIwMTMwNjE0
-MTMzMDAwWhgPMjAyMzA2MTIxMzMwMDhaMAAwggFSMA0GCSqGSIb3DQEBAQUAA4IB
-PwAwggE6AoIBMQCXdtt12OFu2j8tlF4x2Da/kbxyMxFnovJXHNzpx7CE/cGthAR5
-w7Cl92pECog2/d6ryIcjqzzCyCeOVQxIaE3Qz8z6+5UjKh3V/j6CKxcK5g1ER7Qe
-UgpE00ahHzvOpVANtrkYPGC0SFuTFL+PaylH4HW1xBSc1HD5/w7S1k1pDTz9x8ZC
-Z7JOb6NoYsz+rnmWYY2HOG6pyAyQBapIjgzCamgTStA6jTSgoXmCri/dZnJpqjZc
-V6AW7feNmMElhPvL30Cb3QB+9ODjN3pDXRR+Jqilu8ZSrpcvcFHOyKt943id1oC+
-Qu8orA0/kVInX7IuV//TciKzcH5FWz75Kb7hORPzH8M2DQcIKqKKVIwNVeJLKmcG
-RcUGsgTaz2j0JTa6YLJoczuasDWgRMT0goQpAgMBAAGjLzAtMAwGA1UdEwEB/wQC
-MAAwHQYDVR0OBBYEFBW6Si5OY8NPLagdth/JD8R18WMnMA0GCSqGSIb3DQEBCwUA
-A4IBMQAPiHxamUumu203pSVwvpWkpgKKOC2EswyFWQbNC6DWQ3LUkiR7MCiFViYt
-yiIyEh9wtfymWNF9uwaR2nVrJD5mK9Rt7xDiaT5ZOgNjLzmLeYqSlG41mCU1bmqg
-VbxmI1hvPvv3gQ/+WM0lBC6gPGJbVbzlWAIQ1cmevtL1KqOMveZl3VBPxDJD/K9c
-Rbrtx2nBKFDEl6hBljz6gsn4o8pxH3CO7qWpgY/MLwqQzEtTKYnaS9ecywNvj+/F
-ZE4SMoekw6AGRyE14/3i2xW6EmIpxVU4O6ahEFq6r6ZFbdtWnog5vT0y+/tRMgXp
-kCw8puxT2VsYNeJNOybW1IcyN5yluS/FY8iJokdL1JwvhVBVIWaim+T6iwrva7wC
-q1E9Nj30F8UbEkbkNqOdC3UlHQW4
+MIIGbDCCBVSgAwIBAgIBATANBgkqhkiG9w0BAQUFADBFMQswCQYDVQQGEwJBVTET
+MBEGA1UECBMKU29tZS1TdGF0ZTEhMB8GA1UEChMYSW50ZXJuZXQgV2lkZ2l0cyBQ
+dHkgTHRkMB4XDTE2MDUyNDE3NDIyNVoXDTQzMTAxMDE3NDIyNVowVjELMAkGA1UE
+BhMCQVUxEzARBgNVBAgTClNvbWUtU3RhdGUxITAfBgNVBAoTGEludGVybmV0IFdp
+ZGdpdHMgUHR5IEx0ZDEPMA0GA1UEAxMGYWN0aXZlMIGfMA0GCSqGSIb3DQEBAQUA
+A4GNADCBiQKBgQC+GTA1D1+yiXgLqUhJXkSj3hj5FiqlBAfJT/8OSXYifY4M4HYv
+VQrqER2Fs7jdCaeoGWDvwfK/UOV0b1ROnf+T/2bXFs8EOeqjOz4xG2oexNKVrYj9
+ICYAgmSh6Hf2cZJM/YCAISje93Xl2J2w/N7oFC1ZXasPoBIZv3Fgg7hTtQIDAQAB
+o4ID2DCCA9QwCQYDVR0TBAIwADAsBglghkgBhvhCAQ0EHxYdT3BlblNTTCBHZW5l
+cmF0ZWQgQ2VydGlmaWNhdGUwHQYDVR0OBBYEFEynWiCoZK4tLDk3KM1wMsbrz9Ug
+MB8GA1UdIwQYMBaAFND2ZsvHIjITekPKs0ywLfoNEen5MDMGA1UdHwQsMCowKKAm
+oCSGImh0dHA6Ly9sb2NhbGhvc3Q6NTI4MC9kYXRhL2NybC5kZXIwNgYIKwYBBQUH
+AQEEKjAoMCYGCCsGAQUFBzABhhpodHRwOi8vbG9jYWxob3N0OjUyODAvb2NzcDAL
+BgNVHQ8EBAMCBeAwEwYDVR0lBAwwCgYIKwYBBQUHAwkwggLIBgNVHREEggK/MIIC
+u6A4BggrBgEFBQcIBaAsDCp0ZXN0X3NpbmdsZSEjJCVeKigpYH4rLTtfPVtde318
+XEBsb2NhbGhvc3SgPwYIKwYBBQUHCAWgMwwxdGVzdF9zaW5nbGUhIyQlXiooKWB+
+Ky07Xz1bXXt9fFxAbW5lc2lhLmxvY2FsaG9zdKA+BggrBgEFBQcIBaAyDDB0ZXN0
+X3NpbmdsZSEjJCVeKigpYH4rLTtfPVtde318XEBteXNxbC5sb2NhbGhvc3SgPgYI
+KwYBBQUHCAWgMgwwdGVzdF9zaW5nbGUhIyQlXiooKWB+Ky07Xz1bXXt9fFxAcGdz
+cWwubG9jYWxob3N0oD8GCCsGAQUFBwgFoDMMMXRlc3Rfc2luZ2xlISMkJV4qKClg
+fistO189W117fXxcQHNxbGl0ZS5sb2NhbGhvc3SgQAYIKwYBBQUHCAWgNAwydGVz
+dF9zaW5nbGUhIyQlXiooKWB+Ky07Xz1bXXt9fFxAZXh0YXV0aC5sb2NhbGhvc3Sg
+PQYIKwYBBQUHCAWgMQwvdGVzdF9zaW5nbGUhIyQlXiooKWB+Ky07Xz1bXXt9fFxA
+bGRhcC5sb2NhbGhvc3SgPQYIKwYBBQUHCAWgMQwvdGVzdF9zaW5nbGUhIyQlXioo
+KWB+Ky07Xz1bXXt9fFxAcDFkYi5sb2NhbGhvc3SgPQYIKwYBBQUHCAWgMQwvdGVz
+dF9zaW5nbGUhIyQlXiooKWB+Ky07Xz1bXXt9fFxAcmlhay5sb2NhbGhvc3SgPgYI
+KwYBBQUHCAWgMgwwdGVzdF9zaW5nbGUhIyQlXiooKWB+Ky07Xz1bXXt9fFxAcmVk
+aXMubG9jYWxob3N0oD4GCCsGAQUFBwgFoDIMMHRlc3Rfc2luZ2xlISMkJV4qKClg
+fistO189W117fXxcQG1zc3FsLmxvY2FsaG9zdDANBgkqhkiG9w0BAQUFAAOCAQEA
+et4jpmpwlE+2bw+/iqCt7sfU/5nPmQ8YtgMB+32wf7DINNJgkwOdkYJpzhlMXKrh
+/bn8+Ybmq6MbK0r2R91Uu858xQf8VKExQm44qaGSyL5Ug3jsAWb3GLZSaWQo37e9
+QdDeP8XijCEyr3rum19tRIdiImsRAxJqwfaE4pUSgfCEQMkvb+6//8HSf9RRPToD
+o6eAg8QerEtTfxerEdW/0K1ozOrzSrQembWOu+JjvANRl+p59j+1YOWHzS/yQeZl
+K3sjFoCvXPvocRnUznvT+TSdy3ORJSjwfEcP5Crim70amZZ6NeMAxfby9wwmmX0x
+zkwPCSUXliXke6T88Olj7Q==
 -----END CERTIFICATE-----
 -----BEGIN RSA PRIVATE KEY-----
-MIIFegIBAAKCATEAl3bbddjhbto/LZReMdg2v5G8cjMRZ6LyVxzc6cewhP3BrYQE
-ecOwpfdqRAqINv3eq8iHI6s8wsgnjlUMSGhN0M/M+vuVIyod1f4+gisXCuYNREe0
-HlIKRNNGoR87zqVQDba5GDxgtEhbkxS/j2spR+B1tcQUnNRw+f8O0tZNaQ08/cfG
-QmeyTm+jaGLM/q55lmGNhzhuqcgMkAWqSI4MwmpoE0rQOo00oKF5gq4v3WZyaao2
-XFegFu33jZjBJYT7y99Am90AfvTg4zd6Q10UfiaopbvGUq6XL3BRzsirfeN4ndaA
-vkLvKKwNP5FSJ1+yLlf/03Iis3B+RVs++Sm+4TkT8x/DNg0HCCqiilSMDVXiSypn
-BkXFBrIE2s9o9CU2umCyaHM7mrA1oETE9IKEKQIDAQABAoIBMG8X8a4FbowFLhO7
-YD+FC9sFBMhqZpiyLrfwZqReID3bdeRUEYhSHU4OI/ZWF0Tmfh1Xjq992Koxbrn5
-7XFqd7DxybJJN0E8kfe0bJrDCjqnNBHh2d3nZLrIkGR7aT2PiSEV5bs+BdwVun0t
-2bdS7UtX+l5gvJGvTFJBXtkL8GleGV822Vc5gdIAFkXpOdyPkoTXdpw4qwqBnL8/
-TXMYBIgCrXMhEawcNbgPu4iFev2idoU9vXc7ZYD7+8jWB5LJ34cNngguGrnOjLoE
-9c3nZy6uYhhMWtcrSsQlrbN5MtY8w2fPH8nhfA651IxXXVxEajd24t2Csttnl7Vz
-WS5c+oPaWwt67naMrYCWG3q1zWhDqZUAulZR4DzWzGP+idLS/ojCRdTZO9D1O+XP
-fPi0wJECgZkAxh0rTSMCyrJ3VJqEgSPw3yAa1R9cdrTRvV4vRf13Dh8REaHtWt8W
-JeT5WLXL7dOii1St1Fgjo82+4iMqx3PQ2eR/1I6dA7Uy71PaSQTCQnupca2Xx8nT
-5KcrASBkDAudiKog01eC+zYrW+CbUb9AogMZLJzZinlWQ36pJVkWd9SOv25Eqcv6
-zJEmzYKpnow/m8WKNogVGpUCgZkAw7hQxs5VYVLp2XtDqRSmxfJsfsbUVo7tZnSU
-wmejgeNRs7415ZuT142k7qBImrFdYzFcfh2OZnf6D/VIz4Rl7u5YRYRCha/HOGIy
-wTe1huDckJ6lH/BkZ/6f9WSzXnNSNeXQY14WymU5V5qYCAdwECSf+xNuBYNwzA7o
-vOxPE690w3Ox2qghzRjzsBqAMgvqSyKlBpoMckUCgZhyKgD39IL5V5qYcGqHGLUH
-fzK3OdlItq5e19WaGZPv2Us2w/9JbGEQ+UAPNMQNivWSIPwC77+p9zhWjDlssnrZ
-9WkMjhpBNrvhWorhpRJkyWo9jfF3OgEXNJX9kjLVFiRzysYbw8RBC1g1G9ulYfbW
-5b4uDTz3JTDmuCi00v+1khGoktySlG80TzjzGKayLNPC6jTZc9XleQKBmA0STUrJ
-0wf5+qZMxjsPpwfHZhmde+cACrjyBlFpjJELNpSzmnPoTRpzWlWZnN/AAsWyMUQ3
-AyCy2J+iOSeq5wfrITgbWjoFgF+yp0MiTlxgvjpmbg7RBlOvvM0t2ZDwUMhKvf00
-9n6z/f1s1MSMgp6BY7HoHUv++FSYllCv06Qz7q9zFajN29wP046qZm9xPkegW7cy
-KKylAoGYTg94GOWlUTz7Pe9PDrDSFVEAi0LcDmul0ntorvEFDvU2pCRK14gyvl9O
-IJKVyYcDAqA3uvT+zMAniuf8KXNUCcYeEpfzpT+e2eznhczO8hI14M5U0X0LA8P2
-vn0Y+yUWb9Ppu/dcjvaUA+qR/UTHqjAlAr3hFTKRxXFoGwwzTXCXvZGKOnzJRTpj
-LpjI1RG7Weeoyx/8qDs=
+MIICXAIBAAKBgQC+GTA1D1+yiXgLqUhJXkSj3hj5FiqlBAfJT/8OSXYifY4M4HYv
+VQrqER2Fs7jdCaeoGWDvwfK/UOV0b1ROnf+T/2bXFs8EOeqjOz4xG2oexNKVrYj9
+ICYAgmSh6Hf2cZJM/YCAISje93Xl2J2w/N7oFC1ZXasPoBIZv3Fgg7hTtQIDAQAB
+AoGALddtJJ58eVVlOYqs/+RXsRyR8R9DUV/TcNx1qUBV2KNmafyHA4sCgsd10xQv
+9D2rzIGyOp8OpswfSSC/t+WqB9+ezSruzMuX6IURdHZbX6aWWX6maICtPKEEkCmI
+gaLxE/ojuOXnTEBTkVuVWtuFL9PsK/WGi/FIDzJbwqTWJ4ECQQDy9DrBAQM96B6u
+G4XpFzBsfgJZoS+NaMdCwK+/jgcEpI6oxobK8tuGB6drp5jNSuQ905W9n8XjA6Xq
+x8/GH9I5AkEAyE5g05HhMlxBWCq+P70pBDIamdHJcPQVL8+6NXkT+mTqqZxxkUy4
+nMfTh5zE6WfmqYNtrmNBDxXUyaoRSBydXQJACnFnCR7DBekxUGiMc/10LmWoMjQU
+eC6Vyg/APiqbsJ5mJ2kJKDYSK4uurZjxn3lloCa1HAZ/GgfxHMtj6e86OQJAetq3
+wIwE12KGIZF1xpo6gfxJHHbzWngaVozN5OYyPq2O0CDH9xpbUK2vK8oXbCDx9J5L
+s13lFV+Kd3X7y4LhcQJBAKSFg7ht33l8Sa0TdUkY6Tl1NBMCCLf+np+HYrAbQZux
+2NtR6nj2YqeOpEe1ibWZm8tj3dzlTm1FCOIpa+pm114=
 -----END RSA PRIVATE KEY-----
diff --git a/test/ejabberd_SUITE_data/ejabberd.ldif b/test/ejabberd_SUITE_data/ejabberd.ldif
index 863a07e4f..3396ceb4e 100644
--- a/test/ejabberd_SUITE_data/ejabberd.ldif
+++ b/test/ejabberd_SUITE_data/ejabberd.ldif
@@ -15,35 +15,35 @@ ou: groups
 objectClass: organizationalUnit
 
 dn: uid=test_single,ou=users,dc=localhost
-uid: test_single
-mail: test_single@localhost
+uid: test_single!#$%^*()`~+-;_=[]{}|\
+mail: test_single!#$%^*()`~+-;_=[]{}|\@localhost
 objectClass: person
 jpegPhoto:: /9g=
 cn: Test Single
-password: password
+password: password!@#$%^&*()'"`~<>+-/;:_=[]{}|\
 
 dn: uid=test_master,ou=users,dc=localhost
-uid: test_master
-mail: test_master@localhost
+uid: test_master!#$%^*()`~+-;_=[]{}|\
+mail: test_master!#$%^*()`~+-;_=[]{}|\@localhost
 objectClass: person
 jpegPhoto:: /9g=
 cn: Test Master
-password: password
+password: password!@#$%^&*()'"`~<>+-/;:_=[]{}|\
 
 dn: uid=test_slave,ou=users,dc=localhost
-uid: test_slave
-mail: test_slave@localhost
+uid: test_slave!#$%^*()`~+-;_=[]{}|\
+mail: test_slave!#$%^*()`~+-;_=[]{}|\@localhost
 objectClass: person
 jpegPhoto:: /9g=
 cn: Test Slave
-password: password
+password: password!@#$%^&*()'"`~<>+-/;:_=[]{}|\
 
 dn: uid=user2,ou=users,dc=localhost
 uid: user2
 mail: user2@localhost
 objectClass: person
 cn: Test User 2
-password: password
+password: password!@#$%^&*()'"`~<>+-/;:_=[]{}|\
 
 dn: cn=group1,ou=groups,dc=localhost
 objectClass: posixGroup
diff --git a/test/suite.erl b/test/suite.erl
index d4722b81c..f0e3ac93e 100644
--- a/test/suite.erl
+++ b/test/suite.erl
@@ -59,16 +59,16 @@ init_config(Config) ->
     [{server_port, ct:get_config(c2s_port, 5222)},
      {server_host, "localhost"},
      {server, ?COMMON_VHOST},
-     {user, <<"test_single">>},
-     {master_nick, <<"master_nick">>},
-     {slave_nick, <<"slave_nick">>},
-     {room_subject, <<"hello, world!">>},
+     {user, <<"test_single!#$%^*()`~+-;_=[]{}|\\">>},
+     {master_nick, <<"master_nick!@#$%^&*()'\"`~<>+-/;:_=[]{}|\\">>},
+     {slave_nick, <<"slave_nick!@#$%^&*()'\"`~<>+-/;:_=[]{}|\\">>},
+     {room_subject, <<"hello, world!@#$%^&*()'\"`~<>+-/;:_=[]{}|\\">>},
      {certfile, CertFile},
      {base_dir, BaseDir},
-     {resource, <<"resource">>},
-     {master_resource, <<"master_resource">>},
-     {slave_resource, <<"slave_resource">>},
-     {password, <<"password!@#$%^&*()'\"`~<>+-/;:_=[]{}|\\">>},
+     {resource, <<"resource!@#$%^&*()'\"`~<>+-/;:_=[]{}|\\">>},
+     {master_resource, <<"master_resource!@#$%^&*()'\"`~<>+-/;:_=[]{}|\\">>},
+     {slave_resource, <<"slave_resource!@#$%^&*()'\"`~<>+-/;:_=[]{}|\\">>},
+     {password, <<"password!@#$%^&*()'\"`~<>+-/;:_=[]{}|\\">>}
      {backends, get_config_backends()}
      |Config].
 
@@ -317,7 +317,12 @@ sasl_new(<<"DIGEST-MD5">>, User, Server, Password) ->
 		   MyResponse = response(User, Password, Nonce, AuthzId,
 					 Realm, CNonce, DigestURI, NC, QOP,
 					 <<"AUTHENTICATE">>),
-		   Resp = <<"username=\"", User/binary, "\",realm=\"",
+                   SUser = << <<(case Char of
+                                     $" -> <<"\\\"">>;
+                                     $\\ -> <<"\\\\">>;
+                                     _ -> <<Char>>
+                                 end)/binary>> || <<Char>> <= User >>,
+		   Resp = <<"username=\"", SUser/binary, "\",realm=\"",
 			    Realm/binary, "\",nonce=\"", Nonce/binary,
 			    "\",cnonce=\"", CNonce/binary, "\",nc=", NC/binary,
 			    ",qop=", QOP/binary, ",digest-uri=\"",