1
0

PGSQLAuthenticationData.cs 10.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254
  1. /*
  2. * Copyright (c) Contributors, http://opensimulator.org/
  3. * See CONTRIBUTORS.TXT for a full list of copyright holders.
  4. *
  5. * Redistribution and use in source and binary forms, with or without
  6. * modification, are permitted provided that the following conditions are met:
  7. * * Redistributions of source code must retain the above copyright
  8. * notice, this list of conditions and the following disclaimer.
  9. * * Redistributions in binary form must reproduce the above copyright
  10. * notice, this list of conditions and the following disclaimer in the
  11. * documentation and/or other materials provided with the distribution.
  12. * * Neither the name of the OpenSimulator Project nor the
  13. * names of its contributors may be used to endorse or promote products
  14. * derived from this software without specific prior written permission.
  15. *
  16. * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ''AS IS'' AND ANY
  17. * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
  18. * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
  19. * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
  20. * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
  21. * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
  22. * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  23. * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  24. * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
  25. * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  26. */
  27. using System;
  28. using System.Collections;
  29. using System.Collections.Generic;
  30. using OpenMetaverse;
  31. using OpenSim.Framework;
  32. using System.Reflection;
  33. using System.Text;
  34. using System.Data;
  35. using Npgsql;
  36. using NpgsqlTypes;
  37. namespace OpenSim.Data.PGSQL
  38. {
  39. public class PGSQLAuthenticationData : IAuthenticationData
  40. {
  41. private string m_Realm;
  42. private List<string> m_ColumnNames = null;
  43. private int m_LastExpire = 0;
  44. private string m_ConnectionString;
  45. private PGSQLManager m_database;
  46. protected virtual Assembly Assembly
  47. {
  48. get { return GetType().Assembly; }
  49. }
  50. public PGSQLAuthenticationData(string connectionString, string realm)
  51. {
  52. m_Realm = realm;
  53. m_ConnectionString = connectionString;
  54. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  55. {
  56. conn.Open();
  57. Migration m = new Migration(conn, GetType().Assembly, "AuthStore");
  58. m_database = new PGSQLManager(m_ConnectionString);
  59. m.Update();
  60. }
  61. }
  62. public AuthenticationData Get(UUID principalID)
  63. {
  64. AuthenticationData ret = new AuthenticationData();
  65. ret.Data = new Dictionary<string, object>();
  66. string sql = string.Format("select * from {0} where uuid = :principalID", m_Realm);
  67. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  68. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  69. {
  70. cmd.Parameters.Add(m_database.CreateParameter("principalID", principalID));
  71. conn.Open();
  72. using (NpgsqlDataReader result = cmd.ExecuteReader())
  73. {
  74. if (result.Read())
  75. {
  76. ret.PrincipalID = principalID;
  77. if (m_ColumnNames == null)
  78. {
  79. m_ColumnNames = new List<string>();
  80. DataTable schemaTable = result.GetSchemaTable();
  81. foreach (DataRow row in schemaTable.Rows)
  82. m_ColumnNames.Add(row["ColumnName"].ToString());
  83. }
  84. foreach (string s in m_ColumnNames)
  85. {
  86. if (s == "UUID"||s == "uuid")
  87. continue;
  88. ret.Data[s] = result[s].ToString();
  89. }
  90. return ret;
  91. }
  92. }
  93. }
  94. return null;
  95. }
  96. public bool Store(AuthenticationData data)
  97. {
  98. if (data.Data.ContainsKey("UUID"))
  99. data.Data.Remove("UUID");
  100. if (data.Data.ContainsKey("uuid"))
  101. data.Data.Remove("uuid");
  102. /*
  103. Dictionary<string, object> oAuth = new Dictionary<string, object>();
  104. foreach (KeyValuePair<string, object> oDado in data.Data)
  105. {
  106. if (oDado.Key != oDado.Key.ToLower())
  107. {
  108. oAuth.Add(oDado.Key.ToLower(), oDado.Value);
  109. }
  110. }
  111. foreach (KeyValuePair<string, object> oDado in data.Data)
  112. {
  113. if (!oAuth.ContainsKey(oDado.Key.ToLower())) {
  114. oAuth.Add(oDado.Key.ToLower(), oDado.Value);
  115. }
  116. }
  117. */
  118. string[] fields = new List<string>(data.Data.Keys).ToArray();
  119. StringBuilder updateBuilder = new StringBuilder();
  120. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  121. using (NpgsqlCommand cmd = new NpgsqlCommand())
  122. {
  123. updateBuilder.AppendFormat("update {0} set ", m_Realm);
  124. bool first = true;
  125. foreach (string field in fields)
  126. {
  127. if (!first)
  128. updateBuilder.Append(", ");
  129. updateBuilder.AppendFormat("\"{0}\" = :{0}",field);
  130. first = false;
  131. cmd.Parameters.Add(m_database.CreateParameter("" + field, data.Data[field]));
  132. }
  133. updateBuilder.Append(" where uuid = :principalID");
  134. cmd.CommandText = updateBuilder.ToString();
  135. cmd.Connection = conn;
  136. cmd.Parameters.Add(m_database.CreateParameter("principalID", data.PrincipalID));
  137. conn.Open();
  138. if (cmd.ExecuteNonQuery() < 1)
  139. {
  140. StringBuilder insertBuilder = new StringBuilder();
  141. insertBuilder.AppendFormat("insert into {0} (uuid, \"", m_Realm);
  142. insertBuilder.Append(String.Join("\", \"", fields));
  143. insertBuilder.Append("\") values (:principalID, :");
  144. insertBuilder.Append(String.Join(", :", fields));
  145. insertBuilder.Append(")");
  146. cmd.CommandText = insertBuilder.ToString();
  147. if (cmd.ExecuteNonQuery() < 1)
  148. {
  149. return false;
  150. }
  151. }
  152. }
  153. return true;
  154. }
  155. public bool SetDataItem(UUID principalID, string item, string value)
  156. {
  157. string sql = string.Format("update {0} set {1} = :{1} where uuid = :UUID", m_Realm, item);
  158. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  159. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  160. {
  161. cmd.Parameters.Add(m_database.CreateParameter("" + item, value));
  162. conn.Open();
  163. if (cmd.ExecuteNonQuery() > 0)
  164. return true;
  165. }
  166. return false;
  167. }
  168. public bool SetToken(UUID principalID, string token, int lifetime)
  169. {
  170. if (System.Environment.TickCount - m_LastExpire > 30000)
  171. DoExpire();
  172. string sql = "insert into tokens (uuid, token, validity) values (:principalID, :token, :lifetime)";
  173. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  174. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  175. {
  176. cmd.Parameters.Add(m_database.CreateParameter("principalID", principalID));
  177. cmd.Parameters.Add(m_database.CreateParameter("token", token));
  178. cmd.Parameters.Add(m_database.CreateParameter("lifetime", DateTime.Now.AddMinutes(lifetime)));
  179. conn.Open();
  180. if (cmd.ExecuteNonQuery() > 0)
  181. {
  182. return true;
  183. }
  184. }
  185. return false;
  186. }
  187. public bool CheckToken(UUID principalID, string token, int lifetime)
  188. {
  189. if (System.Environment.TickCount - m_LastExpire > 30000)
  190. DoExpire();
  191. DateTime validDate = DateTime.Now.AddMinutes(lifetime);
  192. string sql = "update tokens set validity = :validDate where uuid = :principalID and token = :token and validity > (CURRENT_DATE + CURRENT_TIME)";
  193. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  194. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  195. {
  196. cmd.Parameters.Add(m_database.CreateParameter("principalID", principalID));
  197. cmd.Parameters.Add(m_database.CreateParameter("token", token));
  198. cmd.Parameters.Add(m_database.CreateParameter("validDate", validDate));
  199. conn.Open();
  200. if (cmd.ExecuteNonQuery() > 0)
  201. {
  202. return true;
  203. }
  204. }
  205. return false;
  206. }
  207. private void DoExpire()
  208. {
  209. DateTime currentDateTime = DateTime.Now;
  210. string sql = "delete from tokens where validity < :currentDateTime";
  211. using (NpgsqlConnection conn = new NpgsqlConnection(m_ConnectionString))
  212. using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
  213. {
  214. conn.Open();
  215. cmd.Parameters.Add(m_database.CreateParameter("currentDateTime", currentDateTime));
  216. cmd.ExecuteNonQuery();
  217. }
  218. m_LastExpire = System.Environment.TickCount;
  219. }
  220. }
  221. }