1
0

MSSQLAuthenticationData.cs 9.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227
  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 OpenMetaverse;
  32. using OpenSim.Framework;
  33. using System.Data.SqlClient;
  34. using System.Reflection;
  35. using System.Text;
  36. namespace OpenSim.Data.MSSQL
  37. {
  38. public class MSSQLAuthenticationData : IAuthenticationData
  39. {
  40. private string m_Realm;
  41. private List<string> m_ColumnNames = null;
  42. private int m_LastExpire = 0;
  43. private string m_ConnectionString;
  44. private MSSQLManager m_database;
  45. public MSSQLAuthenticationData(string connectionString, string realm)
  46. {
  47. m_Realm = realm;
  48. m_ConnectionString = connectionString;
  49. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  50. {
  51. conn.Open();
  52. Migration m = new Migration(conn, GetType().Assembly, "AuthStore");
  53. m_database = new MSSQLManager(m_ConnectionString);
  54. m.Update();
  55. }
  56. }
  57. public AuthenticationData Get(UUID principalID)
  58. {
  59. AuthenticationData ret = new AuthenticationData();
  60. ret.Data = new Dictionary<string, object>();
  61. string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm);
  62. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  63. using (SqlCommand cmd = new SqlCommand(sql, conn))
  64. {
  65. cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID));
  66. conn.Open();
  67. using (SqlDataReader result = cmd.ExecuteReader())
  68. {
  69. if (result.Read())
  70. {
  71. ret.PrincipalID = principalID;
  72. if (m_ColumnNames == null)
  73. {
  74. m_ColumnNames = new List<string>();
  75. DataTable schemaTable = result.GetSchemaTable();
  76. foreach (DataRow row in schemaTable.Rows)
  77. m_ColumnNames.Add(row["ColumnName"].ToString());
  78. }
  79. foreach (string s in m_ColumnNames)
  80. {
  81. if (s == "UUID")
  82. continue;
  83. ret.Data[s] = result[s].ToString();
  84. }
  85. return ret;
  86. }
  87. }
  88. }
  89. return null;
  90. }
  91. public bool Store(AuthenticationData data)
  92. {
  93. if (data.Data.ContainsKey("UUID"))
  94. data.Data.Remove("UUID");
  95. string[] fields = new List<string>(data.Data.Keys).ToArray();
  96. StringBuilder updateBuilder = new StringBuilder();
  97. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  98. using (SqlCommand cmd = new SqlCommand())
  99. {
  100. updateBuilder.AppendFormat("update {0} set ", m_Realm);
  101. bool first = true;
  102. foreach (string field in fields)
  103. {
  104. if (!first)
  105. updateBuilder.Append(", ");
  106. updateBuilder.AppendFormat("{0} = @{0}",field);
  107. first = false;
  108. cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[field]));
  109. }
  110. updateBuilder.Append(" where UUID = @principalID");
  111. cmd.CommandText = updateBuilder.ToString();
  112. cmd.Connection = conn;
  113. cmd.Parameters.Add(m_database.CreateParameter("@principalID", data.PrincipalID));
  114. conn.Open();
  115. if (cmd.ExecuteNonQuery() < 1)
  116. {
  117. StringBuilder insertBuilder = new StringBuilder();
  118. insertBuilder.AppendFormat("insert into {0} (UUID, ", m_Realm);
  119. insertBuilder.Append(String.Join(", ", fields));
  120. insertBuilder.Append(") values (@principalID, @");
  121. insertBuilder.Append(String.Join(", @", fields));
  122. insertBuilder.Append(")");
  123. cmd.CommandText = insertBuilder.ToString();
  124. if (cmd.ExecuteNonQuery() < 1)
  125. {
  126. return false;
  127. }
  128. }
  129. }
  130. return true;
  131. }
  132. public bool SetDataItem(UUID principalID, string item, string value)
  133. {
  134. string sql = string.Format("update {0} set {1} = @{1} where UUID = @UUID", m_Realm, item);
  135. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  136. using (SqlCommand cmd = new SqlCommand(sql, conn))
  137. {
  138. cmd.Parameters.Add(m_database.CreateParameter("@" + item, value));
  139. conn.Open();
  140. if (cmd.ExecuteNonQuery() > 0)
  141. return true;
  142. }
  143. return false;
  144. }
  145. public bool SetToken(UUID principalID, string token, int lifetime)
  146. {
  147. if (System.Environment.TickCount - m_LastExpire > 30000)
  148. DoExpire();
  149. string sql = "insert into tokens (UUID, token, validity) values (@principalID, @token, @lifetime)";
  150. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  151. using (SqlCommand cmd = new SqlCommand(sql, conn))
  152. {
  153. cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID));
  154. cmd.Parameters.Add(m_database.CreateParameter("@token", token));
  155. cmd.Parameters.Add(m_database.CreateParameter("@lifetime", DateTime.Now.AddMinutes(lifetime)));
  156. conn.Open();
  157. if (cmd.ExecuteNonQuery() > 0)
  158. {
  159. return true;
  160. }
  161. }
  162. return false;
  163. }
  164. public bool CheckToken(UUID principalID, string token, int lifetime)
  165. {
  166. if (System.Environment.TickCount - m_LastExpire > 30000)
  167. DoExpire();
  168. DateTime validDate = DateTime.Now.AddMinutes(lifetime);
  169. string sql = "update tokens set validity = @validDate where UUID = @principalID and token = @token and validity > GetDate()";
  170. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  171. using (SqlCommand cmd = new SqlCommand(sql, conn))
  172. {
  173. cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID));
  174. cmd.Parameters.Add(m_database.CreateParameter("@token", token));
  175. cmd.Parameters.Add(m_database.CreateParameter("@validDate", validDate));
  176. conn.Open();
  177. if (cmd.ExecuteNonQuery() > 0)
  178. {
  179. return true;
  180. }
  181. }
  182. return false;
  183. }
  184. private void DoExpire()
  185. {
  186. DateTime currentDateTime = DateTime.Now;
  187. string sql = "delete from tokens where validity < @currentDateTime";
  188. using (SqlConnection conn = new SqlConnection(m_ConnectionString))
  189. using (SqlCommand cmd = new SqlCommand(sql, conn))
  190. {
  191. conn.Open();
  192. cmd.Parameters.Add(m_database.CreateParameter("@currentDateTime", currentDateTime));
  193. cmd.ExecuteNonQuery();
  194. }
  195. m_LastExpire = System.Environment.TickCount;
  196. }
  197. }
  198. }