anchor.diff: +%%%-------------------------------------------------------------------
  +%%% File    : sipuserdb_mysql.erl
  +%%% Author  : Magnus Ahltorp <ahltorp@nada.kth.se>
  +%%% Descrip.: MySQL sipuserdb module.
  +%%%
  +%%% Created :  4 Aug 2005 by Magnus Ahltorp <ahltorp@nada.kth.se>
  +%%%-------------------------------------------------------------------
   -module(sipuserdb_mysql).
  +-compile(export_all).
  +
  +-behaviour(sipuserdb).
  +
  +%%--------------------------------------------------------------------
  +%% External exports - sipuserdb callbacks
  +%%--------------------------------------------------------------------
   -export([yxa_init/0,
   	 get_user_with_address/1,
   	 get_users_for_address_of_record/1,
  
positive.code: 	       [Addresses, sipurl:print(URL)]),
      get_users_for_addresses_of_record(Addresses).
  
  pack_password(Password) when binary(Password), size(Password) < 255 ->
      Length = size(Password),
      Padlength = 8 - (((Length + 2) rem 8) - 1),
      Pad = list_to_binary(lists:duplicate(Padlength, 0)),
      <<Length:8,
      Password/binary,
      Pad/binary>>.
  
  unpack_password(Data) when binary(Data) ->
      <<Length:8,
      Rest/binary>> = Data,
      <<Password:Length/binary-unit:8, Rest2/binary>> = Rest,
      binary_to_list(Password).
  
  decrypt_password(IV, Data) ->
      Key1 = sipserver:get_env(password_des3_key1),
      Key2 = sipserver:get_env(password_des3_key2),
      Key3 = sipserver:get_env(password_des3_key3),
      logger:log(debug, "userdb-mysql: IV ~p Data ~p", [IV, Data]),
      Res = crypto:des3_cbc_decrypt(Key1, Key2, Key3, IV, Data),
      logger:log(debug, "userdb-mysql: Res ~p", [unpack_password(Res)]),
      unpack_password(Res).
  
  encrypt_password(IV, Data) ->
      Key1 = sipserver:get_env(password_des3_key1),
      Key2 = sipserver:get_env(password_des3_key2),
      Key3 = sipserver:get_env(password_des3_key3),
      logger:log(debug, "userdb-mysql: IV ~p Data ~p", [IV, Data]),
      Res = binary_to_list(crypto:des3_cbc_encrypt(Key1, Key2, Key3, IV,
  						 pack_password(list_to_binary(Data)))),
      logger:log(debug, "userdb-mysql: Res ~p", [Res]),
      Res.
  
  % Salt with kthid
  
  calc_password_hmac(Data, Kthid) ->
      Key = sipserver:get_env(password_shamac_key),
      logger:log(debug, "userdb-mysql: Key ~p Data ~p Kthid ~p", [Key, Data, Kthid]),
      Res = binary_to_list(crypto:sha_mac(Key, Kthid ++ ":" ++ Data)),
      logger:log(debug, "userdb-mysql: Res ~p", [Res]),
      Res.
  
  check_password_hmac(Mac, Data, Kthid) ->
      case calc_password_hmac(Data, Kthid) of
  	Mac ->
  	    true;
  	_ ->
  	    false
      end.
  
  set_password_for_user(User, Password) ->
      IV = shellget("openssl rand 8"),
      logger:log(debug, "userdb-mysql: IV: ~p", [IV]),
      Data = encrypt_password(IV, Password),
      case mysql:fetch("insert into password (kthid,iv,mac,data) values (" ++
  		     mysql:quote(User) ++ "," ++
  		     mysql:quote(binary_to_list(IV)) ++ "," ++
  		     mysql:quote(calc_password_hmac(Data, User)) ++ "," ++
  		     mysql:quote(Data) ++ ")") of
  	{error, Message} ->
  	    logger:log(error, "userdb-mysql: Error for kthid ~p: ~p", [User, Message]),
  	    error;
  	{_, Message} ->
  	    logger:log(debug, "userdb-mysql: set_password_for_user: ~p", [Message]),
  	    nomatch
      end.    
  
  
  shellget(Command) ->
      Port = open_port({spawn, Command}, [stream, binary, eof]),
      shellgetloop(Port, <<>>).
  
  shellgetloop(Port, Acc) ->
      receive
  	{Port, {data, Data}} ->
  	    shellgetloop(Port, <<Data/binary, Acc/binary>>);
  	{Port, eof} ->
  	    Acc
      end.
      
  
  
  %% Attribute fetching functions :
  %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  
  get_password_for_user(User) ->
  %    sipuserdb_mysql:set_password_for_user("u1lhi9k2", "foobar"),
      case mysql:fetch("select iv,mac,data from password where kthid = " ++ mysql:quote(User)) of
  	{error, Message} ->
  	    logger:log(error, "userdb-mysql: Error for kthid ~p: ~p", [User, Message]),
  	    error;
  	{_, []} ->
  	    logger:log(debug, "userdb-mysql: No such kthid ~p when fetching password", [User]),
  	    nomatch;
  	{_, [[IV, Mac, Data], _]} ->
  	    Password = decrypt_password(IV, Data),
  	    case check_password_hmac(Mac, Data, User) of
  		true ->
  		    logger:log(debug, "userdb-mysql: pwd: ~p", [Password]),
  		    Password;
  		_ ->
  		    nomatch
  	    end
      end.
  
  get_classes_for_user(User) ->
      case mysql:fetch("select class from classes where kthid = " ++ mysql:quote(User)) of
  	{error, Message} ->
  	    logger:log(error, "userdb-mysql: Error for username ~p: ~p", [User, Message]),
  	    error;
  	{_, Classes} ->
  	    lists:map(fun ([Class]) ->
  			      list_to_atom(Class)
  		      end, Classes)
      end.
  
  get_telephonenumber_for_user(User) ->
      case mysql:fetch("select number from numbers where kthid = " ++ mysql:quote(User)) of
  	{_, []} ->
  	    logger:log(debug, "userdb-mysql: No numbers for user ~p", [User]),
  	    nomatch;
  	{error, Message} ->
  	    logger:log(error, "userdb-mysql: Error for username ~p: ~p", [User, Message]),
  	    error;
  	{_, [[FirstNumber] | _]} ->
  	    FirstNumber
      end.
  
  get_forwards_for_users(In) ->
      nomatch.
  
  get_forward_for_user(User) ->
      nomatch.
  
negative.code: 	 get_classes_for_user/1,
  	 get_telephonenumber_for_user/1,
  	 get_forwards_for_users/1,
  	 get_forward_for_user/1
  	]).
  
  %%--------------------------------------------------------------------
  %% External exports - module specific exported functions
  %%--------------------------------------------------------------------
  -export([make_sql_statement/2
  	]).
  
  %%--------------------------------------------------------------------
  %% Include files
  %%--------------------------------------------------------------------
  -include("siprecords.hrl").
  
  %%====================================================================
  %% External functions
  %%====================================================================
  
  %%--------------------------------------------------------------------
  %% Function: yxa_init()
  %% Descrip.: Perform any necessary startup initialization and
  %%           return an OTP supervisor child spec if we want to add
  %%           to sipserver_sup's list. If this sipuserdb_module
  %%           needs to be persistent, it should be a gen_server and
  %%           init should just return a spec so that the gen_server
  %%           is started by the supervisor.
  %% Returns : Spec |
  %%           []
  %%           Spec = OTP supervisor child specification
  %%--------------------------------------------------------------------
  yxa_init() ->
      {ok, [Host, User, Password, Db]} =
  	get_mysql_params([sipuserdb_mysql_host,
  			  sipuserdb_mysql_user,
  			  sipuserdb_mysql_password,
  			  sipuserdb_mysql_database
  			 ]),
      %% create a logging function that makes the MySQL subsystem logs
      %% end up in the standard Yxa log system.
      LogFun = fun(Level, Fmt, Args) ->
  		     logger:log(Level, Fmt, Args)
  	     end,
      %% mysql server port is optional
      MysqlArgs =
  	case yxa_config:get_env(sipuserdb_mysql_port) of
  	    {ok, Port} ->
  		[yxa, Host, Port, User, Password, Db, LogFun];
  	    none ->
  		[yxa, Host, User, Password, Db, LogFun]
  	end,
      [{mysql_dispatcher, {mysql, start_link, MysqlArgs},
        permanent, 2000, worker, [mysql_dispatcher]}
      ].
  
  %% part of yxa_init/0
  get_mysql_params(L) ->
      get_mysql_params(L, []).
  
  %% part of yxa_init/0
  get_mysql_params([H | T], Res) ->
      case yxa_config:get_env(H) of
  	{ok, Value} ->
  	    get_mysql_params(T, [Value | Res]);
  	none ->
  	    Msg = lists:concat(["sipuserdb_mysql requires you to set the configuration parameter ",
  				H]),
  	    %% throw as atom to get printed readably in the startup error message
  	    throw(list_to_atom(Msg))
      end;
  get_mysql_params([], Res) ->
      {ok, lists:reverse(Res)}.
  
  
  %%--------------------------------------------------------------------
  %% Function: get_user_with_address(Address)
  %%           Address = string(), an address in string format.
  %% Descrip.: Looks up exactly one user with an Address. Used for
  %%           example in REGISTER. If there are multiple users with
  %%           this address in our database, this function returns
  %%           'error'.
  %% Returns:  Username |
  %%           nomatch  |
  %%           error
  %%           Username = string()
  %%--------------------------------------------------------------------
  get_user_with_address(Address) ->
      Query1 = make_sql_statement(sipuserdb_mysql_get_user,
  				Address),
      case mysql:fetch(yxa, Query1) of
  	{ok, _, []} ->
  	    Query2 = make_sql_statement(sipuserdb_mysql_get_user_for_address,
  					     Address),
  	    case mysql:fetch(yxa, Query2) of
  		{ok, _, []} ->
  		    logger:log(debug, "userdb-mysql: No user with name or number ~p", [Address]),
  		    nomatch;
  		{ok, _, [[User]]} ->
  		    User;
  		{ok, _, Users} ->
  		    logger:log(debug, "userdb-mysql: More than one user with number ~p (~p)", [Address, lists:append(Users)]),
  		    error;
  		{error, Reason} ->
  		    logger:log(error, "userdb-mysql: Error for number ~p: ~p", [Address, Reason]),
  		    error
  	    end;
  	{ok, _, [[User]]} ->
  	    User;
  	{ok, _, Users} ->
  	    logger:log(debug, "userdb-mysql: More than one user with username ~p (~p)", [Address, lists:append(Users)]),
  	    error;
  	{error, Reason} ->
  	    logger:log(error, "userdb-mysql: Error for address ~p: ~p", [Address, Reason]),
  	    error
      end.
  
  
  %%--------------------------------------------------------------------
  %% Function: get_users_for_address_of_record(Address)
  %%           Address = string(), an address in string format.
  %% Descrip.: Get all usernames of users matching an address. Used to
  %%           find out to which users we should send a request.
  %% Returns : Users |
  %%           error
  %%           Users = list() of string()
  %%--------------------------------------------------------------------
  get_users_for_address_of_record(Address) ->
      get_users_for_addresses_of_record([Address]).
  
  
  %%--------------------------------------------------------------------
  %% Function: get_users_for_addresses_of_record(In)
  %%           In = list() of string(), addresses in string format.
  %% Descrip.: Iterate over a list of addresses of record, return
  %%           all users matching one or more of the addresses,
  %%           without duplicates.
  %% Returns : Users, list() of string()
  %%--------------------------------------------------------------------
  get_users_for_addresses_of_record(AddressList) ->
      Query1 = make_sql_statement(sipuserdb_mysql_get_user_for_address,
  				AddressList),
      case mysql:fetch(yxa, Query1) of
  	{ok, _, Users} ->
  	    Res = lists:usort(lists:append(Users)),
  	    logger:log(debug, "userdb-mysql: Found user(s) ~p for address(es) ~p", [Res, AddressList]),
  	    Res;
  	{error, Reason} ->
  	    logger:log(error, "userdb-mysql: Error for address(es) ~p: ~p", [AddressList, Reason]),
  	    error
      end.
  
  %%--------------------------------------------------------------------
  %% Function: get_addresses_for_users(In)
  %%           In = list() of string(), usernames
  %% Descrip.: Iterate over a list of users, return all their
  %%           addresses without duplicates by using the next
  %%           function, get_addresses_for_user/1.
  %% Returns : Addresses, list() of string()
  %%--------------------------------------------------------------------
  get_addresses_for_users(In) when is_list(In) ->
      Addresses = 
  	lists:foldl(fun(User, Acc) ->
  			    case get_addresses_for_user(User) of
  				nomatch ->
  				    Acc;
  				A when is_list(A) ->
  				    [A | Acc]
  			    end
  		    end, [], In),
      lists:usort(lists:append(Addresses)).
  
  %%--------------------------------------------------------------------
  %% Function: get_addresses_for_user(Username)
  %%           Username = string()
  %% Descrip.: Get all possible addresses of a user. Both configured
  %%           ones, and implicit ones. Used for example to check if a
  %%           request from a user has an acceptable From: header.
  %% Returns : Addresses |
  %%           error
  %%           Addresses = list() of string()
  %%--------------------------------------------------------------------
  get_addresses_for_user(User) ->
      Query1 = make_sql_statement(sipuserdb_mysql_get_addresses_for_user,
  				User),
      case mysql:fetch(yxa, Query1) of
  	{ok, _, []} ->
  	    logger:log(debug, "userdb-mysql: No numbers for user ~p", [User]),
  	    %% Check if there is a user with that name, if so we canonify the username
  	    Query2 = make_sql_statement(sipuserdb_mysql_get_user,
  					User),
  	    case mysql:fetch(yxa, Query2) of
  		{ok, _, []} ->
  		    logger:log(debug, "userdb-mysql: No such user ~p", [User]),
  		    nomatch;
  		{ok, _, _} ->
  		    [local:canonify_user(User)];
  		{error, Reason} ->
  		    logger:log(error, "userdb-mysql: Error for username ~p: ~p", [User, Reason]),
  		    error
  	    end;
  	{ok, _, Numbers} ->
  	    FlatNumbers = lists:append(Numbers),
  	    logger:log(debug, "userdb-mysql: Found number(s) ~p for user ~p",
  		       [FlatNumbers, User]),
  	    CanonL = [local:canonify_user(User)],
  	    NumberL = local:canonify_numberlist(FlatNumbers),
  	    All = lists:append([CanonL, NumberL]),
  	    lists:usort(All);
  	{error, Reason} ->
  	    logger:log(error, "userdb-mysql: Error for username ~p: ~p", [User, Reason]),
  	    error
      end.
  
  
  %%--------------------------------------------------------------------
  %% Function: get_users_for_url(URL)
  %%           URL = sipurl record()
  %% Descrip.: Given an URL that is typically the Request-URI of an
  %%           incoming request, make a list of implicit user
  %%           addresses and return a list of all users matching any
  %%           of these addresses. This is located in here since
  %%           user database backends can have their own way of
  %%           deriving addresses from a Request-URI.
  %% Returns : Usernames, list() of string()
  %%--------------------------------------------------------------------
  get_users_for_url(URL) when record(URL, sipurl) ->
      Addresses = local:lookup_url_to_addresses(sipuserdb_mysql, URL),
  