1
0

SQLiteAuthenticationData.cs 9.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266
  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 System.Data;
  31. using System.Reflection;
  32. using log4net;
  33. using OpenMetaverse;
  34. using OpenSim.Framework;
  35. using Mono.Data.SqliteClient;
  36. namespace OpenSim.Data.SQLiteLegacy
  37. {
  38. public class SQLiteAuthenticationData : SQLiteFramework, IAuthenticationData
  39. {
  40. private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
  41. private string m_Realm;
  42. private List<string> m_ColumnNames;
  43. private int m_LastExpire;
  44. private string m_connectionString;
  45. protected static SqliteConnection m_Connection;
  46. private static bool m_initialized = false;
  47. public SQLiteAuthenticationData(string connectionString, string realm)
  48. : base(connectionString)
  49. {
  50. m_Realm = realm;
  51. m_connectionString = connectionString;
  52. if (!m_initialized)
  53. {
  54. m_Connection = new SqliteConnection(connectionString);
  55. m_Connection.Open();
  56. using (SqliteConnection dbcon = (SqliteConnection)((ICloneable)m_Connection).Clone())
  57. {
  58. dbcon.Open();
  59. Migration m = new Migration(dbcon, GetType().Assembly, "AuthStore");
  60. m.Update();
  61. dbcon.Close();
  62. }
  63. m_initialized = true;
  64. }
  65. }
  66. public AuthenticationData Get(UUID principalID)
  67. {
  68. AuthenticationData ret = new AuthenticationData();
  69. ret.Data = new Dictionary<string, object>();
  70. SqliteCommand cmd = new SqliteCommand("select * from `" + m_Realm + "` where UUID = :PrincipalID");
  71. cmd.Parameters.Add(new SqliteParameter(":PrincipalID", principalID.ToString()));
  72. IDataReader result = ExecuteReader(cmd, m_Connection);
  73. try
  74. {
  75. if (result.Read())
  76. {
  77. ret.PrincipalID = principalID;
  78. if (m_ColumnNames == null)
  79. {
  80. m_ColumnNames = new List<string>();
  81. DataTable schemaTable = result.GetSchemaTable();
  82. foreach (DataRow row in schemaTable.Rows)
  83. m_ColumnNames.Add(row["ColumnName"].ToString());
  84. }
  85. foreach (string s in m_ColumnNames)
  86. {
  87. if (s == "UUID")
  88. continue;
  89. ret.Data[s] = result[s].ToString();
  90. }
  91. return ret;
  92. }
  93. else
  94. {
  95. return null;
  96. }
  97. }
  98. catch
  99. {
  100. }
  101. finally
  102. {
  103. CloseCommand(cmd);
  104. }
  105. return null;
  106. }
  107. public bool Store(AuthenticationData data)
  108. {
  109. if (data.Data.ContainsKey("UUID"))
  110. data.Data.Remove("UUID");
  111. string[] fields = new List<string>(data.Data.Keys).ToArray();
  112. string[] values = new string[data.Data.Count];
  113. int i = 0;
  114. foreach (object o in data.Data.Values)
  115. values[i++] = o.ToString();
  116. SqliteCommand cmd = new SqliteCommand();
  117. if (Get(data.PrincipalID) != null)
  118. {
  119. string update = "update `" + m_Realm + "` set ";
  120. bool first = true;
  121. foreach (string field in fields)
  122. {
  123. if (!first)
  124. update += ", ";
  125. update += "`" + field + "` = :" + field;
  126. cmd.Parameters.Add(new SqliteParameter(":" + field, data.Data[field]));
  127. first = false;
  128. }
  129. update += " where UUID = :UUID";
  130. cmd.Parameters.Add(new SqliteParameter(":UUID", data.PrincipalID.ToString()));
  131. cmd.CommandText = update;
  132. try
  133. {
  134. if (ExecuteNonQuery(cmd, m_Connection) < 1)
  135. {
  136. CloseCommand(cmd);
  137. return false;
  138. }
  139. }
  140. catch (Exception e)
  141. {
  142. m_log.Error("[SQLITE]: Exception storing authentication data", e);
  143. CloseCommand(cmd);
  144. return false;
  145. }
  146. }
  147. else
  148. {
  149. string insert = "insert into `" + m_Realm + "` (`UUID`, `" +
  150. String.Join("`, `", fields) +
  151. "`) values (:UUID, :" + String.Join(", :", fields) + ")";
  152. cmd.Parameters.Add(new SqliteParameter(":UUID", data.PrincipalID.ToString()));
  153. foreach (string field in fields)
  154. cmd.Parameters.Add(new SqliteParameter(":" + field, data.Data[field]));
  155. cmd.CommandText = insert;
  156. try
  157. {
  158. if (ExecuteNonQuery(cmd, m_Connection) < 1)
  159. {
  160. CloseCommand(cmd);
  161. return false;
  162. }
  163. }
  164. catch (Exception e)
  165. {
  166. Console.WriteLine(e.ToString());
  167. CloseCommand(cmd);
  168. return false;
  169. }
  170. }
  171. CloseCommand(cmd);
  172. return true;
  173. }
  174. public bool SetDataItem(UUID principalID, string item, string value)
  175. {
  176. SqliteCommand cmd = new SqliteCommand("update `" + m_Realm +
  177. "` set `" + item + "` = " + value + " where UUID = '" + principalID.ToString() + "'");
  178. if (ExecuteNonQuery(cmd, m_Connection) > 0)
  179. return true;
  180. return false;
  181. }
  182. public bool SetToken(UUID principalID, string token, int lifetime)
  183. {
  184. if (System.Environment.TickCount - m_LastExpire > 30000)
  185. DoExpire();
  186. SqliteCommand cmd = new SqliteCommand("insert into tokens (UUID, token, validity) values ('" + principalID.ToString() +
  187. "', '" + token + "', datetime('now', 'localtime', '+" + lifetime.ToString() + " minutes'))");
  188. if (ExecuteNonQuery(cmd, m_Connection) > 0)
  189. {
  190. cmd.Dispose();
  191. return true;
  192. }
  193. cmd.Dispose();
  194. return false;
  195. }
  196. public bool CheckToken(UUID principalID, string token, int lifetime)
  197. {
  198. if (System.Environment.TickCount - m_LastExpire > 30000)
  199. DoExpire();
  200. SqliteCommand cmd = new SqliteCommand("update tokens set validity = datetime('now', 'localtime', '+" + lifetime.ToString() +
  201. " minutes') where UUID = '" + principalID.ToString() + "' and token = '" + token + "' and validity > datetime('now', 'localtime')");
  202. if (ExecuteNonQuery(cmd, m_Connection) > 0)
  203. {
  204. cmd.Dispose();
  205. return true;
  206. }
  207. cmd.Dispose();
  208. return false;
  209. }
  210. private void DoExpire()
  211. {
  212. SqliteCommand cmd = new SqliteCommand("delete from tokens where validity < datetime('now', 'localtime')");
  213. ExecuteNonQuery(cmd, m_Connection);
  214. cmd.Dispose();
  215. m_LastExpire = System.Environment.TickCount;
  216. }
  217. }
  218. }