123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221 |
- /*
- * Copyright (c) Contributors, http://opensimulator.org/
- * See CONTRIBUTORS.TXT for a full list of copyright holders.
- *
- * Redistribution and use in source and binary forms, with or without
- * modification, are permitted provided that the following conditions are met:
- * * Redistributions of source code must retain the above copyright
- * notice, this list of conditions and the following disclaimer.
- * * Redistributions in binary form must reproduce the above copyright
- * notice, this list of conditions and the following disclaimer in the
- * documentation and/or other materials provided with the distribution.
- * * Neither the name of the OpenSimulator Project nor the
- * names of its contributors may be used to endorse or promote products
- * derived from this software without specific prior written permission.
- *
- * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ''AS IS'' AND ANY
- * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
- * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
- * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
- * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
- * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
- * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
- * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
- * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
- * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
- */
- using System;
- using System.Collections;
- using System.Collections.Generic;
- using System.Data;
- using OpenMetaverse;
- using OpenSim.Framework;
- using System.Data.SqlClient;
- using System.Reflection;
- using System.Text;
- namespace OpenSim.Data.MSSQL
- {
- public class MSSQLAuthenticationData : IAuthenticationData
- {
- private string m_Realm;
- private List<string> m_ColumnNames = null;
- private int m_LastExpire = 0;
- private string m_ConnectionString;
- private MSSQLManager m_database;
- public MSSQLAuthenticationData(string connectionString, string realm)
- {
- m_Realm = realm;
- m_ConnectionString = connectionString;
- using (SqlConnection conn = new SqlConnection(m_ConnectionString))
- {
- conn.Open();
- Migration m = new Migration(conn, GetType().Assembly, "AuthStore");
- m.Update();
- }
- }
- public AuthenticationData Get(UUID principalID)
- {
- AuthenticationData ret = new AuthenticationData();
- ret.Data = new Dictionary<string, object>();
- string sql = string.Format("select * from {0} where UUID = @principalID", m_Realm);
- using (SqlConnection conn = new SqlConnection(m_ConnectionString))
- using (SqlCommand cmd = new SqlCommand(sql, conn))
- {
- cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID));
- conn.Open();
- using (SqlDataReader result = cmd.ExecuteReader())
- {
- if (result.Read())
- {
- ret.PrincipalID = principalID;
- if (m_ColumnNames == null)
- {
- m_ColumnNames = new List<string>();
- DataTable schemaTable = result.GetSchemaTable();
- foreach (DataRow row in schemaTable.Rows)
- m_ColumnNames.Add(row["ColumnName"].ToString());
- }
- foreach (string s in m_ColumnNames)
- {
- if (s == "UUID")
- continue;
- ret.Data[s] = result[s].ToString();
- }
- return ret;
- }
- }
- }
- return null;
- }
- public bool Store(AuthenticationData data)
- {
- if (data.Data.ContainsKey("UUID"))
- data.Data.Remove("UUID");
- string[] fields = new List<string>(data.Data.Keys).ToArray();
- StringBuilder updateBuilder = new StringBuilder();
-
- using (SqlConnection conn = new SqlConnection(m_ConnectionString))
- using (SqlCommand cmd = new SqlCommand())
- {
- updateBuilder.AppendFormat("update {0} set ", m_Realm);
- bool first = true;
- foreach (string field in fields)
- {
- if (!first)
- updateBuilder.Append(", ");
- updateBuilder.AppendFormat("{0} = @{0}",field);
- first = false;
- cmd.Parameters.Add(m_database.CreateParameter("@" + field, data.Data[field]));
- }
- updateBuilder.Append(" where UUID = @principalID");
- cmd.CommandText = updateBuilder.ToString();
- cmd.Connection = conn;
- cmd.Parameters.Add(m_database.CreateParameter("@principalID", data.PrincipalID));
-
- conn.Open();
- if (cmd.ExecuteNonQuery() < 1)
- {
- StringBuilder insertBuilder = new StringBuilder();
- insertBuilder.AppendFormat("insert into {0} (UUID, ", m_Realm);
- insertBuilder.Append(String.Join(", ", fields));
- insertBuilder.Append(") values (@principalID, @");
- insertBuilder.Append(String.Join(", @", fields));
- insertBuilder.Append(")");
- cmd.CommandText = insertBuilder.ToString();
- if (cmd.ExecuteNonQuery() < 1)
- {
- return false;
- }
- }
- }
- return true;
- }
- public bool SetDataItem(UUID principalID, string item, string value)
- {
- string sql = string.Format("update {0} set {1} = @{1} where UUID = @UUID", m_Realm, item);
- using (SqlConnection conn = new SqlConnection(m_ConnectionString))
- using (SqlCommand cmd = new SqlCommand(sql, conn))
- {
- cmd.Parameters.Add(m_database.CreateParameter("@" + item, value));
- conn.Open();
- if (cmd.ExecuteNonQuery() > 0)
- return true;
- }
- return false;
- }
- public bool SetToken(UUID principalID, string token, int lifetime)
- {
- if (System.Environment.TickCount - m_LastExpire > 30000)
- DoExpire();
- string sql = "insert into tokens (UUID, token, validity) values (@principalID, @token, date_add(now(), interval @lifetime minute))";
- using (SqlConnection conn = new SqlConnection(m_ConnectionString))
- using (SqlCommand cmd = new SqlCommand(sql, conn))
- {
- cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID));
- cmd.Parameters.Add(m_database.CreateParameter("@token", token));
- cmd.Parameters.Add(m_database.CreateParameter("@lifetime", lifetime));
- conn.Open();
- if (cmd.ExecuteNonQuery() > 0)
- {
- return true;
- }
- }
- return false;
- }
- public bool CheckToken(UUID principalID, string token, int lifetime)
- {
- if (System.Environment.TickCount - m_LastExpire > 30000)
- DoExpire();
- string sql = "update tokens set validity = date_add(now(), interval @lifetime minute) where UUID = @principalID and token = @token and validity > now()";
- using (SqlConnection conn = new SqlConnection(m_ConnectionString))
- using (SqlCommand cmd = new SqlCommand(sql, conn))
- {
- cmd.Parameters.Add(m_database.CreateParameter("@principalID", principalID));
- cmd.Parameters.Add(m_database.CreateParameter("@token", token));
- cmd.Parameters.Add(m_database.CreateParameter("@lifetime", lifetime));
- conn.Open();
- if (cmd.ExecuteNonQuery() > 0)
- {
- return true;
- }
- }
- return false;
- }
- private void DoExpire()
- {
- string sql = "delete from tokens where validity < now()";
- using (SqlConnection conn = new SqlConnection(m_ConnectionString))
- using (SqlCommand cmd = new SqlCommand(sql, conn))
- {
- conn.Open();
- cmd.ExecuteNonQuery();
- }
- m_LastExpire = System.Environment.TickCount;
- }
- }
- }
|