PageRenderTime 28ms CodeModel.GetById 22ms RepoModel.GetById 0ms app.codeStats 1ms

/mcs/class/System.Web/System.Web.Security/SqliteMembershipProvider.cs

https://github.com/ztfuqingvip/mono
C# | 1639 lines | 1238 code | 246 blank | 155 comment | 142 complexity | 2b79de0dcfce5548cf63c02975fefea9 MD5 | raw file
Possible License(s): GPL-2.0, Unlicense, MPL-2.0-no-copyleft-exception, CC-BY-SA-3.0
  1. //
  2. //
  3. // Permission is hereby granted, free of charge, to any person obtaining
  4. // a copy of this software and associated documentation files (the
  5. // "Software"), to deal in the Software without restriction, including
  6. // without limitation the rights to use, copy, modify, merge, publish,
  7. // distribute, sublicense, and/or sell copies of the Software, and to
  8. // permit persons to whom the Software is furnished to do so, subject to
  9. // the following conditions:
  10. //
  11. // The above copyright notice and this permission notice shall be
  12. // included in all copies or substantial portions of the Software.
  13. //
  14. // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  15. // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  16. // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  17. // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  18. // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  19. // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  20. // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  21. //
  22. // Copyright � 2006, 2007 Nauck IT KG http://www.nauck-it.de
  23. //
  24. // Author:
  25. // Daniel Nauck <d.nauck(at)nauck-it.de>
  26. //
  27. // Adapted to Sqlite by Marek Habersack <mhabersack@novell.com>
  28. //
  29. #if NET_2_0
  30. using System;
  31. using System.Data;
  32. using System.Data.Common;
  33. using System.Collections.Generic;
  34. using System.Collections.Specialized;
  35. using System.Text;
  36. using System.Security.Cryptography;
  37. using System.Web.Hosting;
  38. using System.Web.Configuration;
  39. using System.Web.Security;
  40. using System.Configuration;
  41. using System.Configuration.Provider;
  42. using System.Diagnostics;
  43. using Mono.Data.Sqlite;
  44. namespace System.Web.Security
  45. {
  46. internal class SqliteMembershipProvider : MembershipProvider
  47. {
  48. const string m_TableName = "Users";
  49. string m_ConnectionString = string.Empty;
  50. const int m_NewPasswordLength = 8;
  51. bool machineKeyIsAutoGenerated;
  52. // Used when determining encryption key values.
  53. MachineKeySection m_MachineKey = null;
  54. DbParameter AddParameter (DbCommand command, string parameterName, object parameterValue)
  55. {
  56. return AddParameter (command, parameterName, ParameterDirection.Input, parameterValue);
  57. }
  58. DbParameter AddParameter (DbCommand command, string parameterName, ParameterDirection direction, object parameterValue)
  59. {
  60. DbParameter dbp = command.CreateParameter ();
  61. dbp.ParameterName = parameterName;
  62. dbp.Value = parameterValue;
  63. dbp.Direction = direction;
  64. command.Parameters.Add (dbp);
  65. return dbp;
  66. }
  67. /// <summary>
  68. /// System.Configuration.Provider.ProviderBase.Initialize Method.
  69. /// </summary>
  70. public override void Initialize(string name, NameValueCollection config)
  71. {
  72. // Initialize values from web.config.
  73. if (config == null)
  74. throw new ArgumentNullException("Config", Properties.Resources.ErrArgumentNull);
  75. if (string.IsNullOrEmpty(name))
  76. name = Properties.Resources.MembershipProviderDefaultName;
  77. if (string.IsNullOrEmpty(config["description"]))
  78. {
  79. config.Remove("description");
  80. config.Add("description", Properties.Resources.MembershipProviderDefaultDescription);
  81. }
  82. // Initialize the abstract base class.
  83. base.Initialize(name, config);
  84. m_ApplicationName = GetConfigValue(config["applicationName"], HostingEnvironment.ApplicationVirtualPath);
  85. m_MaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
  86. m_PasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
  87. m_MinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"));
  88. m_MinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
  89. m_PasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""));
  90. m_EnablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
  91. m_EnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
  92. m_RequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
  93. m_RequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));
  94. // Get password encryption type.
  95. string pwFormat = GetConfigValue(config["passwordFormat"], "Hashed");
  96. switch (pwFormat)
  97. {
  98. case "Hashed":
  99. m_PasswordFormat = MembershipPasswordFormat.Hashed;
  100. break;
  101. case "Encrypted":
  102. m_PasswordFormat = MembershipPasswordFormat.Encrypted;
  103. break;
  104. case "Clear":
  105. m_PasswordFormat = MembershipPasswordFormat.Clear;
  106. break;
  107. default:
  108. throw new ProviderException(Properties.Resources.ErrPwFormatNotSupported);
  109. }
  110. // Get connection string.
  111. string connStrName = config["connectionStringName"];
  112. if (string.IsNullOrEmpty(connStrName))
  113. {
  114. throw new ArgumentOutOfRangeException("ConnectionStringName", Properties.Resources.ErrArgumentNullOrEmpty);
  115. }
  116. else
  117. {
  118. ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings[connStrName];
  119. if (ConnectionStringSettings == null || string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
  120. {
  121. throw new ProviderException(Properties.Resources.ErrConnectionStringNullOrEmpty);
  122. }
  123. m_ConnectionString = ConnectionStringSettings.ConnectionString;
  124. }
  125. // Get encryption and decryption key information from the configuration.
  126. m_MachineKey = (MachineKeySection)WebConfigurationManager.GetSection("system.web/machineKey", null);
  127. if (!m_PasswordFormat.Equals(MembershipPasswordFormat.Clear))
  128. {
  129. if (m_MachineKey == null)
  130. throw new ArgumentNullException("system.web/machineKey", Properties.Resources.ErrArgumentNull);
  131. if (m_MachineKey.ValidationKey.Contains("AutoGenerate")) {
  132. machineKeyIsAutoGenerated = true;
  133. if (m_PasswordFormat.Equals (MembershipPasswordFormat.Encrypted))
  134. throw new ProviderException(Properties.Resources.ErrAutoGeneratedKeyNotSupported);
  135. }
  136. }
  137. }
  138. /// <summary>
  139. /// System.Web.Security.MembershipProvider properties.
  140. /// </summary>
  141. #region System.Web.Security.MembershipProvider properties
  142. string m_ApplicationName = string.Empty;
  143. bool m_EnablePasswordReset = false;
  144. bool m_EnablePasswordRetrieval = false;
  145. bool m_RequiresQuestionAndAnswer = false;
  146. bool m_RequiresUniqueEmail = false;
  147. int m_MaxInvalidPasswordAttempts = 0;
  148. int m_PasswordAttemptWindow = 0;
  149. MembershipPasswordFormat m_PasswordFormat = MembershipPasswordFormat.Clear;
  150. int m_MinRequiredNonAlphanumericCharacters = 0;
  151. int m_MinRequiredPasswordLength = 0;
  152. string m_PasswordStrengthRegularExpression = string.Empty;
  153. public override string ApplicationName
  154. {
  155. get { return m_ApplicationName; }
  156. set { m_ApplicationName = value; }
  157. }
  158. public override bool EnablePasswordReset
  159. {
  160. get { return m_EnablePasswordReset; }
  161. }
  162. public override bool EnablePasswordRetrieval
  163. {
  164. get { return m_EnablePasswordRetrieval; }
  165. }
  166. public override bool RequiresQuestionAndAnswer
  167. {
  168. get { return m_RequiresQuestionAndAnswer; }
  169. }
  170. public override bool RequiresUniqueEmail
  171. {
  172. get { return m_RequiresUniqueEmail; }
  173. }
  174. public override int MaxInvalidPasswordAttempts
  175. {
  176. get { return m_MaxInvalidPasswordAttempts; }
  177. }
  178. public override int PasswordAttemptWindow
  179. {
  180. get { return m_PasswordAttemptWindow; }
  181. }
  182. public override MembershipPasswordFormat PasswordFormat
  183. {
  184. get { return m_PasswordFormat; }
  185. }
  186. public override int MinRequiredNonAlphanumericCharacters
  187. {
  188. get { return m_MinRequiredNonAlphanumericCharacters; }
  189. }
  190. public override int MinRequiredPasswordLength
  191. {
  192. get { return m_MinRequiredPasswordLength; }
  193. }
  194. public override string PasswordStrengthRegularExpression
  195. {
  196. get { return m_PasswordStrengthRegularExpression; }
  197. }
  198. #endregion
  199. /// <summary>
  200. /// System.Web.Security.MembershipProvider methods.
  201. /// </summary>
  202. #region System.Web.Security.MembershipProvider methods
  203. /// <summary>
  204. /// MembershipProvider.ChangePassword
  205. /// </summary>
  206. public override bool ChangePassword(string username, string oldPassword, string newPassword)
  207. {
  208. if (!ValidateUser(username, oldPassword))
  209. return false;
  210. ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
  211. OnValidatingPassword(args);
  212. if (args.Cancel)
  213. {
  214. if (args.FailureInformation != null)
  215. throw args.FailureInformation;
  216. else
  217. throw new MembershipPasswordException(Properties.Resources.ErrPasswordChangeCanceled);
  218. }
  219. int rowsAffected = 0;
  220. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  221. {
  222. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  223. {
  224. dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"Password\" = @Password, \"LastPasswordChangedDate\" = @LastPasswordChangedDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  225. AddParameter (dbCommand,"@Password", EncodePassword(newPassword));
  226. AddParameter (dbCommand,"@LastPasswordChangedDate", DateTime.Now);
  227. AddParameter (dbCommand,"@Username", username);
  228. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  229. try
  230. {
  231. dbConn.Open();
  232. dbCommand.Prepare();
  233. rowsAffected = dbCommand.ExecuteNonQuery();
  234. }
  235. catch (SqliteException e)
  236. {
  237. Trace.WriteLine(e.ToString());
  238. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  239. }
  240. finally
  241. {
  242. if (dbConn != null)
  243. dbConn.Close();
  244. }
  245. }
  246. }
  247. if (rowsAffected > 0)
  248. return true;
  249. else
  250. return false;
  251. }
  252. /// <summary>
  253. /// MembershipProvider.ChangePasswordQuestionAndAnswer
  254. /// </summary>
  255. public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
  256. {
  257. if (!ValidateUser(username, password))
  258. return false;
  259. int rowsAffected = 0;
  260. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  261. {
  262. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  263. {
  264. dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"PasswordQuestion\" = @PasswordQuestion, \"PasswordAnswer\" = @PasswordAnswer WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  265. AddParameter (dbCommand,"@PasswordQuestion", newPasswordQuestion);
  266. AddParameter (dbCommand,"@PasswordAnswer", EncodePassword(newPasswordAnswer));
  267. AddParameter (dbCommand,"@Username", username);
  268. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  269. try
  270. {
  271. dbConn.Open();
  272. dbCommand.Prepare();
  273. rowsAffected = dbCommand.ExecuteNonQuery();
  274. }
  275. catch (SqliteException e)
  276. {
  277. Trace.WriteLine(e.ToString());
  278. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  279. }
  280. finally
  281. {
  282. if (dbConn != null)
  283. dbConn.Close();
  284. }
  285. }
  286. }
  287. if (rowsAffected > 0)
  288. return true;
  289. else
  290. return false;
  291. }
  292. /// <summary>
  293. /// MembershipProvider.CreateUser
  294. /// </summary>
  295. public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved,
  296. object providerUserKey, out MembershipCreateStatus status)
  297. {
  298. ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);
  299. OnValidatingPassword(args);
  300. if (args.Cancel)
  301. {
  302. status = MembershipCreateStatus.InvalidPassword;
  303. return null;
  304. }
  305. if (RequiresUniqueEmail && string.IsNullOrEmpty(email))
  306. {
  307. status = MembershipCreateStatus.InvalidEmail;
  308. return null;
  309. }
  310. if (RequiresUniqueEmail && !string.IsNullOrEmpty(GetUserNameByEmail(email)))
  311. {
  312. status = MembershipCreateStatus.DuplicateEmail;
  313. return null;
  314. }
  315. if (GetUser(username, false) == null)
  316. {
  317. DateTime createDate = DateTime.Now;
  318. if (providerUserKey == null)
  319. {
  320. providerUserKey = Guid.NewGuid();
  321. }
  322. else
  323. {
  324. if (!(providerUserKey is Guid))
  325. {
  326. status = MembershipCreateStatus.InvalidProviderUserKey;
  327. return null;
  328. }
  329. }
  330. // Create user in database
  331. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  332. {
  333. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  334. {
  335. dbCommand.CommandText = string.Format("INSERT INTO \"{0}\" (\"pId\", \"Username\", \"Password\", \"Email\", \"PasswordQuestion\", \"PasswordAnswer\", \"IsApproved\", \"CreationDate\", \"LastPasswordChangedDate\", \"LastActivityDate\", \"ApplicationName\", \"IsLockedOut\", \"LastLockedOutDate\", \"FailedPasswordAttemptCount\", \"FailedPasswordAttemptWindowStart\", \"FailedPasswordAnswerAttemptCount\", \"FailedPasswordAnswerAttemptWindowStart\") Values (@pId, @Username, @Password, @Email, @PasswordQuestion, @PasswordAnswer, @IsApproved, @CreationDate, @LastPasswordChangedDate, @LastActivityDate, @ApplicationName, @IsLockedOut, @LastLockedOutDate, @FailedPasswordAttemptCount, @FailedPasswordAttemptWindowStart, @FailedPasswordAnswerAttemptCount, @FailedPasswordAnswerAttemptWindowStart)", m_TableName);
  336. AddParameter (dbCommand,"@pId", providerUserKey);
  337. AddParameter (dbCommand,"@Username", username);
  338. AddParameter (dbCommand,"@Password", EncodePassword(password));
  339. AddParameter (dbCommand,"@Email", email);
  340. AddParameter (dbCommand,"@PasswordQuestion", passwordQuestion);
  341. AddParameter (dbCommand,"@PasswordAnswer", EncodePassword(passwordAnswer));
  342. AddParameter (dbCommand,"@IsApproved", isApproved);
  343. AddParameter (dbCommand,"@CreationDate", createDate);
  344. AddParameter (dbCommand,"@LastPasswordChangedDate", createDate);
  345. AddParameter (dbCommand,"@LastActivityDate", createDate);
  346. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  347. AddParameter (dbCommand,"@IsLockedOut", false);
  348. AddParameter (dbCommand,"@LastLockedOutDate", createDate);
  349. AddParameter (dbCommand,"@FailedPasswordAttemptCount", 0);
  350. AddParameter (dbCommand,"@FailedPasswordAttemptWindowStart", createDate);
  351. AddParameter (dbCommand,"@FailedPasswordAnswerAttemptCount", 0);
  352. AddParameter (dbCommand,"@FailedPasswordAnswerAttemptWindowStart", createDate);
  353. try
  354. {
  355. dbConn.Open();
  356. dbCommand.Prepare();
  357. if (dbCommand.ExecuteNonQuery() > 0)
  358. {
  359. status = MembershipCreateStatus.Success;
  360. }
  361. else
  362. {
  363. status = MembershipCreateStatus.UserRejected;
  364. }
  365. }
  366. catch (SqliteException e)
  367. {
  368. status = MembershipCreateStatus.ProviderError;
  369. Trace.WriteLine(e.ToString());
  370. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  371. }
  372. finally
  373. {
  374. if (dbConn != null)
  375. dbConn.Close();
  376. }
  377. return GetUser(username, false);
  378. }
  379. }
  380. }
  381. else
  382. {
  383. status = MembershipCreateStatus.DuplicateUserName;
  384. }
  385. return null;
  386. }
  387. /// <summary>
  388. /// MembershipProvider.DeleteUser
  389. /// </summary>
  390. public override bool DeleteUser(string username, bool deleteAllRelatedData)
  391. {
  392. int rowsAffected = 0;
  393. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  394. {
  395. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  396. {
  397. dbCommand.CommandText = string.Format("DELETE FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  398. AddParameter (dbCommand,"@Username", username);
  399. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  400. try
  401. {
  402. dbConn.Open();
  403. dbCommand.Prepare();
  404. rowsAffected = dbCommand.ExecuteNonQuery();
  405. if (deleteAllRelatedData)
  406. {
  407. // Process commands to delete all data for the user in the database.
  408. }
  409. }
  410. catch (SqliteException e)
  411. {
  412. Trace.WriteLine(e.ToString());
  413. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  414. }
  415. finally
  416. {
  417. if (dbConn != null)
  418. dbConn.Close();
  419. }
  420. }
  421. }
  422. if (rowsAffected > 0)
  423. return true;
  424. else
  425. return false;
  426. }
  427. /// <summary>
  428. /// MembershipProvider.FindUsersByEmail
  429. /// </summary>
  430. public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
  431. {
  432. totalRecords = 0;
  433. MembershipUserCollection users = new MembershipUserCollection();
  434. // replace permitted wildcard characters
  435. emailToMatch = emailToMatch.Replace('*','%');
  436. emailToMatch = emailToMatch.Replace('?', '_');
  437. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  438. {
  439. // Get user count
  440. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  441. {
  442. dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"Email\" LIKE @Email AND \"ApplicationName\" = @ApplicationName", m_TableName);
  443. AddParameter (dbCommand,"@Email", emailToMatch);
  444. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  445. try
  446. {
  447. dbConn.Open();
  448. dbCommand.Prepare();
  449. Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out totalRecords);
  450. if (totalRecords <= 0) { return users; }
  451. }
  452. catch (SqliteException e)
  453. {
  454. Trace.WriteLine(e.ToString());
  455. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  456. }
  457. finally
  458. {
  459. if (dbConn != null)
  460. dbConn.Close();
  461. }
  462. }
  463. // Fetch user from database
  464. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  465. {
  466. dbCommand.CommandText = string.Format("SELECT \"pId\", \"Username\", \"Email\", \"PasswordQuestion\", \"Comment\", \"IsApproved\", \"IsLockedOut\", \"CreationDate\", \"LastLoginDate\", \"LastActivityDate\", \"LastPasswordChangedDate\", \"LastLockedOutDate\" FROM \"{0}\" WHERE \"Email\" LIKE @Email AND \"ApplicationName\" = @ApplicationName ORDER BY \"Username\" ASC LIMIT @MaxCount OFFSET @StartIndex", m_TableName);
  467. AddParameter (dbCommand,"@Email", emailToMatch);
  468. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  469. AddParameter (dbCommand,"@MaxCount", pageSize);
  470. AddParameter (dbCommand,"@StartIndex", pageSize * pageIndex);
  471. try
  472. {
  473. dbConn.Open();
  474. dbCommand.Prepare();
  475. using (SqliteDataReader reader = dbCommand.ExecuteReader())
  476. {
  477. while (reader.Read())
  478. {
  479. MembershipUser u = GetUserFromReader(reader);
  480. users.Add(u);
  481. }
  482. }
  483. }
  484. catch (SqliteException e)
  485. {
  486. Trace.WriteLine(e.ToString());
  487. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  488. }
  489. finally
  490. {
  491. if (dbConn != null)
  492. dbConn.Close();
  493. }
  494. }
  495. }
  496. return users;
  497. }
  498. /// <summary>
  499. /// MembershipProvider.FindUsersByName
  500. /// </summary>
  501. public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
  502. {
  503. totalRecords = 0;
  504. MembershipUserCollection users = new MembershipUserCollection();
  505. // replace permitted wildcard characters
  506. usernameToMatch = usernameToMatch.Replace('*', '%');
  507. usernameToMatch = usernameToMatch.Replace('?', '_');
  508. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  509. {
  510. // Get user count
  511. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  512. {
  513. dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"Username\" LIKE @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  514. AddParameter (dbCommand,"@Username", usernameToMatch);
  515. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  516. try
  517. {
  518. dbConn.Open();
  519. dbCommand.Prepare();
  520. Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out totalRecords);
  521. if (totalRecords <= 0) { return users; }
  522. }
  523. catch (SqliteException e)
  524. {
  525. Trace.WriteLine(e.ToString());
  526. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  527. }
  528. finally
  529. {
  530. if (dbConn != null)
  531. dbConn.Close();
  532. }
  533. }
  534. // Fetch user from database
  535. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  536. {
  537. dbCommand.CommandText = string.Format("SELECT \"pId\", \"Username\", \"Email\", \"PasswordQuestion\", \"Comment\", \"IsApproved\", \"IsLockedOut\", \"CreationDate\", \"LastLoginDate\", \"LastActivityDate\", \"LastPasswordChangedDate\", \"LastLockedOutDate\" FROM \"{0}\" WHERE \"Username\" LIKE @Username AND \"ApplicationName\" = @ApplicationName ORDER BY \"Username\" ASC LIMIT @MaxCount OFFSET @StartIndex", m_TableName);
  538. AddParameter (dbCommand,"@Username", usernameToMatch);
  539. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  540. AddParameter (dbCommand,"@MaxCount", pageSize);
  541. AddParameter (dbCommand,"@StartIndex", pageSize * pageIndex);
  542. try
  543. {
  544. dbConn.Open();
  545. dbCommand.Prepare();
  546. using (SqliteDataReader reader = dbCommand.ExecuteReader())
  547. {
  548. while (reader.Read())
  549. {
  550. MembershipUser u = GetUserFromReader(reader);
  551. users.Add(u);
  552. }
  553. }
  554. }
  555. catch (SqliteException e)
  556. {
  557. Trace.WriteLine(e.ToString());
  558. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  559. }
  560. finally
  561. {
  562. if (dbConn != null)
  563. dbConn.Close();
  564. }
  565. }
  566. }
  567. return users;
  568. }
  569. /// <summary>
  570. /// MembershipProvider.GetAllUsers
  571. /// </summary>
  572. public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
  573. {
  574. totalRecords = 0;
  575. MembershipUserCollection users = new MembershipUserCollection();
  576. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  577. {
  578. // Get user count
  579. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  580. {
  581. dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"ApplicationName\" = @ApplicationName", m_TableName);
  582. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  583. try
  584. {
  585. dbConn.Open();
  586. dbCommand.Prepare();
  587. Int32.TryParse(dbCommand.ExecuteScalar().ToString(), out totalRecords);
  588. if (totalRecords <= 0) { return users; }
  589. }
  590. catch (SqliteException e)
  591. {
  592. Trace.WriteLine(e.ToString());
  593. throw e;
  594. }
  595. finally
  596. {
  597. if (dbConn != null)
  598. dbConn.Close();
  599. }
  600. }
  601. // Fetch user from database
  602. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  603. {
  604. dbCommand.CommandText = string.Format("SELECT \"pId\", \"Username\", \"Email\", \"PasswordQuestion\", \"Comment\", \"IsApproved\", \"IsLockedOut\", \"CreationDate\", \"LastLoginDate\", \"LastActivityDate\", \"LastPasswordChangedDate\", \"LastLockedOutDate\" FROM \"{0}\" WHERE \"ApplicationName\" = @ApplicationName ORDER BY \"Username\" ASC LIMIT @MaxCount OFFSET @StartIndex", m_TableName);
  605. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  606. AddParameter (dbCommand,"@MaxCount", pageSize);
  607. AddParameter (dbCommand,"@StartIndex", pageSize * pageIndex);
  608. try
  609. {
  610. dbConn.Open();
  611. dbCommand.Prepare();
  612. using (SqliteDataReader reader = dbCommand.ExecuteReader())
  613. {
  614. while (reader.Read())
  615. {
  616. MembershipUser u = GetUserFromReader(reader);
  617. users.Add(u);
  618. }
  619. }
  620. }
  621. catch (SqliteException e)
  622. {
  623. Trace.WriteLine(e.ToString());
  624. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  625. }
  626. finally
  627. {
  628. if (dbConn != null)
  629. dbConn.Close();
  630. }
  631. }
  632. }
  633. return users;
  634. }
  635. /// <summary>
  636. /// MembershipProvider.GetNumberOfUsersOnline
  637. /// </summary>
  638. public override int GetNumberOfUsersOnline()
  639. {
  640. int numOnline = 0;
  641. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  642. {
  643. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  644. {
  645. TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
  646. DateTime compareTime = DateTime.Now.Subtract(onlineSpan);
  647. dbCommand.CommandText = string.Format("SELECT Count(*) FROM \"{0}\" WHERE \"LastActivityDate\" > @CompareTime AND \"ApplicationName\" = @ApplicationName", m_TableName);
  648. AddParameter (dbCommand,"@CompareTime", compareTime);
  649. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  650. try
  651. {
  652. dbConn.Open();
  653. dbCommand.Prepare();
  654. numOnline = (int)dbCommand.ExecuteScalar();
  655. }
  656. catch (SqliteException e)
  657. {
  658. Trace.WriteLine(e.ToString());
  659. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  660. }
  661. finally
  662. {
  663. if (dbConn != null)
  664. dbConn.Close();
  665. }
  666. }
  667. }
  668. return numOnline;
  669. }
  670. /// <summary>
  671. /// MembershipProvider.GetPassword
  672. /// </summary>
  673. public override string GetPassword(string username, string answer)
  674. {
  675. if (!EnablePasswordRetrieval)
  676. {
  677. throw new ProviderException(Properties.Resources.ErrPasswordRetrievalNotEnabled);
  678. }
  679. if (PasswordFormat == MembershipPasswordFormat.Hashed)
  680. {
  681. throw new ProviderException(Properties.Resources.ErrCantRetrieveHashedPw);
  682. }
  683. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  684. {
  685. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  686. {
  687. dbCommand.CommandText = string.Format("SELECT \"Password\", \"PasswordAnswer\", \"IsLockedOut\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  688. AddParameter (dbCommand,"@Username", username);
  689. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  690. try
  691. {
  692. dbConn.Open();
  693. dbCommand.Prepare();
  694. using (SqliteDataReader reader = dbCommand.ExecuteReader())
  695. {
  696. if (reader.HasRows)
  697. {
  698. reader.Read();
  699. string password = reader.GetString(0);
  700. string passwordAnswer = reader.GetString(1);
  701. bool isLockedOut = reader.GetBoolean(2);
  702. reader.Close();
  703. if (isLockedOut)
  704. throw new MembershipPasswordException(Properties.Resources.ErrUserIsLoggedOut);
  705. if (m_RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
  706. {
  707. UpdateFailureCount(username, FailureType.PasswordAnswer);
  708. throw new MembershipPasswordException(Properties.Resources.ErrIncorrectPasswordAnswer);
  709. }
  710. if (m_PasswordFormat == MembershipPasswordFormat.Encrypted)
  711. {
  712. password = UnEncodePassword(password);
  713. }
  714. return password;
  715. }
  716. else
  717. {
  718. throw new MembershipPasswordException(Properties.Resources.ErrUserNotFound);
  719. }
  720. }
  721. }
  722. catch (SqliteException e)
  723. {
  724. Trace.WriteLine(e.ToString());
  725. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  726. }
  727. finally
  728. {
  729. if (dbConn != null)
  730. dbConn.Close();
  731. }
  732. }
  733. }
  734. }
  735. /// <summary>
  736. /// MembershipProvider.GetUser
  737. /// </summary>
  738. public override MembershipUser GetUser(string username, bool userIsOnline)
  739. {
  740. MembershipUser u = null;
  741. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  742. {
  743. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  744. {
  745. dbCommand.CommandText = string.Format("SELECT \"pId\", \"Username\", \"Email\", \"PasswordQuestion\", \"Comment\", \"IsApproved\", \"IsLockedOut\", \"CreationDate\", \"LastLoginDate\", \"LastActivityDate\", \"LastPasswordChangedDate\", \"LastLockedOutDate\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  746. AddParameter (dbCommand,"@Username", username);
  747. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  748. try
  749. {
  750. dbConn.Open();
  751. dbCommand.Prepare();
  752. using (SqliteDataReader reader = dbCommand.ExecuteReader())
  753. {
  754. if (reader.HasRows)
  755. {
  756. reader.Read();
  757. u = GetUserFromReader(reader);
  758. if (userIsOnline)
  759. {
  760. // Update user online status
  761. using (SqliteCommand dbUpdateCommand = dbConn.CreateCommand())
  762. {
  763. dbUpdateCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastActivityDate\" = @LastActivityDate WHERE \"pId\" = @pId", m_TableName);
  764. AddParameter (dbUpdateCommand, "@LastActivityDate", DateTime.Now);
  765. AddParameter (dbUpdateCommand, "@pId", u.ProviderUserKey);
  766. dbUpdateCommand.Prepare();
  767. dbUpdateCommand.ExecuteNonQuery();
  768. }
  769. }
  770. }
  771. }
  772. }
  773. catch (SqliteException e)
  774. {
  775. Trace.WriteLine(e.ToString());
  776. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  777. }
  778. finally
  779. {
  780. if (dbConn != null)
  781. dbConn.Close();
  782. }
  783. }
  784. }
  785. return u;
  786. }
  787. /// <summary>
  788. /// MembershipProvider.GetUser
  789. /// </summary>
  790. public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
  791. {
  792. MembershipUser u = null;
  793. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  794. {
  795. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  796. {
  797. dbCommand.CommandText = string.Format("SELECT \"pId\", \"Username\", \"Email\", \"PasswordQuestion\", \"Comment\", \"IsApproved\", \"IsLockedOut\", \"CreationDate\", \"LastLoginDate\", \"LastActivityDate\", \"LastPasswordChangedDate\", \"LastLockedOutDate\" FROM \"{0}\" WHERE \"pId\" = @pId", m_TableName);
  798. AddParameter (dbCommand,"@pId", providerUserKey);
  799. try
  800. {
  801. dbConn.Open();
  802. dbCommand.Prepare();
  803. using (SqliteDataReader reader = dbCommand.ExecuteReader())
  804. {
  805. if (reader.HasRows)
  806. {
  807. reader.Read();
  808. u = GetUserFromReader(reader);
  809. if (userIsOnline)
  810. {
  811. // Update user online status
  812. using (SqliteCommand dbUpdateCommand = dbConn.CreateCommand())
  813. {
  814. dbUpdateCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastActivityDate\" = @LastActivityDate WHERE \"pId\" = @pId", m_TableName);
  815. AddParameter (dbUpdateCommand, "@LastActivityDate", DateTime.Now);
  816. AddParameter (dbUpdateCommand, "@pId", u.ProviderUserKey);
  817. dbUpdateCommand.Prepare();
  818. dbUpdateCommand.ExecuteNonQuery();
  819. }
  820. }
  821. }
  822. }
  823. }
  824. catch (SqliteException e)
  825. {
  826. Trace.WriteLine(e.ToString());
  827. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  828. }
  829. finally
  830. {
  831. if (dbConn != null)
  832. dbConn.Close();
  833. }
  834. }
  835. }
  836. return u;
  837. }
  838. /// <summary>
  839. /// MembershipProvider.GetUserNameByEmail
  840. /// </summary>
  841. public override string GetUserNameByEmail(string email)
  842. {
  843. string username = string.Empty;
  844. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  845. {
  846. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  847. {
  848. dbCommand.CommandText = string.Format("SELECT \"Username\" FROM \"{0}\" WHERE \"Email\" = @Email AND \"ApplicationName\" = @ApplicationName", m_TableName);
  849. AddParameter (dbCommand,"@Email", email);
  850. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  851. try
  852. {
  853. dbConn.Open();
  854. dbCommand.Prepare();
  855. username = (dbCommand.ExecuteScalar() as string) ?? string.Empty;
  856. }
  857. catch (SqliteException e)
  858. {
  859. Trace.WriteLine(e.ToString());
  860. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  861. }
  862. finally
  863. {
  864. if (dbConn != null)
  865. dbConn.Close();
  866. }
  867. }
  868. }
  869. return username;
  870. }
  871. /// <summary>
  872. /// MembershipProvider.ResetPassword
  873. /// </summary>
  874. public override string ResetPassword(string username, string answer)
  875. {
  876. if (!m_EnablePasswordReset)
  877. {
  878. throw new NotSupportedException(Properties.Resources.ErrPasswordResetNotEnabled);
  879. }
  880. if (string.IsNullOrEmpty(answer) && m_RequiresQuestionAndAnswer)
  881. {
  882. UpdateFailureCount(username, FailureType.PasswordAnswer);
  883. throw new ProviderException(Properties.Resources.ErrPasswordAnswerRequired);
  884. }
  885. string newPassword = Membership.GeneratePassword(m_NewPasswordLength, m_MinRequiredNonAlphanumericCharacters);
  886. ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
  887. OnValidatingPassword(args);
  888. if (args.Cancel)
  889. {
  890. if (args.FailureInformation != null)
  891. throw args.FailureInformation;
  892. else
  893. throw new MembershipPasswordException(Properties.Resources.ErrPasswordResetCanceled);
  894. }
  895. int rowsAffected = 0;
  896. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  897. {
  898. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  899. {
  900. dbCommand.CommandText = string.Format("SELECT \"PasswordAnswer\", \"IsLockedOut\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  901. AddParameter (dbCommand,"@Username", username);
  902. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  903. try
  904. {
  905. string passwordAnswer = string.Empty;
  906. dbConn.Open();
  907. dbCommand.Prepare();
  908. using (SqliteDataReader reader = dbCommand.ExecuteReader())
  909. {
  910. if (reader.HasRows)
  911. {
  912. reader.Read();
  913. passwordAnswer = reader.GetString(0);
  914. bool isLockedOut = reader.GetBoolean(1);
  915. reader.Close();
  916. if (isLockedOut)
  917. throw new MembershipPasswordException(Properties.Resources.ErrUserIsLoggedOut);
  918. if (m_RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
  919. {
  920. UpdateFailureCount(username, FailureType.PasswordAnswer);
  921. throw new MembershipPasswordException(Properties.Resources.ErrIncorrectPasswordAnswer);
  922. }
  923. }
  924. else
  925. {
  926. throw new MembershipPasswordException(Properties.Resources.ErrUserNotFound);
  927. }
  928. }
  929. // Reset Password
  930. using (SqliteCommand dbUpdateCommand = dbConn.CreateCommand())
  931. {
  932. dbUpdateCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"Password\" = @Password, \"LastPasswordChangedDate\" = @LastPasswordChangedDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsLockedOut\" = @IsLockedOut", m_TableName);
  933. AddParameter (dbUpdateCommand, "@Password", EncodePassword(newPassword));
  934. AddParameter (dbUpdateCommand, "@LastPasswordChangedDate", DateTime.Now);
  935. AddParameter (dbUpdateCommand, "@Username", username);
  936. AddParameter (dbUpdateCommand, "@ApplicationName", m_ApplicationName);
  937. AddParameter (dbUpdateCommand, "@IsLockedOut", false);
  938. dbUpdateCommand.Prepare();
  939. rowsAffected = dbUpdateCommand.ExecuteNonQuery();
  940. }
  941. }
  942. catch (SqliteException e)
  943. {
  944. Trace.WriteLine(e.ToString());
  945. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  946. }
  947. finally
  948. {
  949. if (dbConn != null)
  950. dbConn.Close();
  951. }
  952. }
  953. }
  954. if (rowsAffected > 0)
  955. return newPassword;
  956. else
  957. throw new MembershipPasswordException(Properties.Resources.ErrPasswordResetAborted);
  958. }
  959. /// <summary>
  960. /// MembershipProvider.UnlockUser
  961. /// </summary>
  962. public override bool UnlockUser(string userName)
  963. {
  964. int rowsAffected = 0;
  965. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  966. {
  967. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  968. {
  969. dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"IsLockedOut\" = @IsLockedOut, \"LastLockedOutDate\" = @LastLockedOutDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  970. AddParameter (dbCommand,"@IsLockedOut", false);
  971. AddParameter (dbCommand,"@LastLockedOutDate", DateTime.Now);
  972. AddParameter (dbCommand,"@Username", userName);
  973. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  974. try
  975. {
  976. dbConn.Open();
  977. dbCommand.Prepare();
  978. rowsAffected = dbCommand.ExecuteNonQuery();
  979. }
  980. catch (SqliteException e)
  981. {
  982. Trace.WriteLine(e.ToString());
  983. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  984. }
  985. finally
  986. {
  987. if (dbConn != null)
  988. dbConn.Close();
  989. }
  990. }
  991. }
  992. if (rowsAffected > 0)
  993. return true;
  994. else
  995. return false;
  996. }
  997. /// <summary>
  998. /// MembershipProvider.UpdateUser
  999. /// </summary>
  1000. public override void UpdateUser(MembershipUser user)
  1001. {
  1002. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  1003. {
  1004. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  1005. {
  1006. dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"Email\" = @Email, \"Comment\" = @Comment, \"IsApproved\" = @IsApproved WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  1007. AddParameter (dbCommand,"@Email", user.Email);
  1008. AddParameter (dbCommand,"@Comment", user.Comment);
  1009. AddParameter (dbCommand,"@IsApproved", user.IsApproved);
  1010. AddParameter (dbCommand,"@Username", user.UserName);
  1011. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  1012. try
  1013. {
  1014. dbConn.Open();
  1015. dbCommand.Prepare();
  1016. dbCommand.ExecuteNonQuery();
  1017. }
  1018. catch (SqliteException e)
  1019. {
  1020. Trace.WriteLine(e.ToString());
  1021. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  1022. }
  1023. finally
  1024. {
  1025. if (dbConn != null)
  1026. dbConn.Close();
  1027. }
  1028. }
  1029. }
  1030. }
  1031. /// <summary>
  1032. /// MembershipProvider.ValidateUser
  1033. /// </summary>
  1034. public override bool ValidateUser(string username, string password)
  1035. {
  1036. string dbPassword = string.Empty;
  1037. bool dbIsApproved = false;
  1038. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  1039. {
  1040. // Fetch user data from database
  1041. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  1042. {
  1043. dbCommand.CommandText = string.Format("SELECT \"Password\", \"IsApproved\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsLockedOut\" = @IsLockedOut", m_TableName);
  1044. AddParameter (dbCommand,"@Username", username);
  1045. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  1046. AddParameter (dbCommand,"@IsLockedOut", false);
  1047. try
  1048. {
  1049. dbConn.Open();
  1050. dbCommand.Prepare();
  1051. using (SqliteDataReader reader = dbCommand.ExecuteReader())
  1052. {
  1053. if (reader.HasRows)
  1054. {
  1055. reader.Read();
  1056. dbPassword = reader.GetString(0);
  1057. dbIsApproved = reader.GetBoolean(1);
  1058. }
  1059. else
  1060. {
  1061. return false;
  1062. }
  1063. }
  1064. }
  1065. catch (SqliteException e)
  1066. {
  1067. Trace.WriteLine(e.ToString());
  1068. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  1069. }
  1070. finally
  1071. {
  1072. if (dbConn != null)
  1073. dbConn.Close();
  1074. }
  1075. }
  1076. if (CheckPassword(password, dbPassword))
  1077. {
  1078. if (dbIsApproved)
  1079. {
  1080. // Update last login date
  1081. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  1082. {
  1083. dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"LastLoginDate\" = @LastLoginDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  1084. AddParameter (dbCommand,"@LastLoginDate", DateTime.Now);
  1085. AddParameter (dbCommand,"@Username", username);
  1086. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  1087. try
  1088. {
  1089. dbConn.Open();
  1090. dbCommand.Prepare();
  1091. dbCommand.ExecuteNonQuery();
  1092. return true;
  1093. }
  1094. catch (SqliteException e)
  1095. {
  1096. Trace.WriteLine(e.ToString());
  1097. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  1098. }
  1099. finally
  1100. {
  1101. if (dbConn != null)
  1102. dbConn.Close();
  1103. }
  1104. }
  1105. }
  1106. }
  1107. return false;
  1108. }
  1109. }
  1110. #endregion
  1111. #region private methods
  1112. /// <summary>
  1113. /// A helper function to retrieve config values from the configuration file.
  1114. /// </summary>
  1115. /// <param name="configValue"></param>
  1116. /// <param name="defaultValue"></param>
  1117. /// <returns></returns>
  1118. string GetConfigValue(string configValue, string defaultValue)
  1119. {
  1120. if (string.IsNullOrEmpty(configValue))
  1121. return defaultValue;
  1122. return configValue;
  1123. }
  1124. /// <summary>
  1125. /// A helper function that takes the current row from the SqliteDataReader
  1126. /// and hydrates a MembershipUser from the values. Called by the
  1127. /// MembershipUser.GetUser implementation.
  1128. /// </summary>
  1129. /// <param name="reader">SqliteDataReader object</param>
  1130. /// <returns>MembershipUser object</returns>
  1131. MembershipUser GetUserFromReader(SqliteDataReader reader)
  1132. {
  1133. object providerUserKey = reader.GetValue(0);
  1134. string username = reader.GetString(1);
  1135. string email = string.Empty;
  1136. if (!reader.IsDBNull(2))
  1137. email = reader.GetString(2);
  1138. string passwordQuestion = string.Empty;
  1139. if (!reader.IsDBNull(3))
  1140. passwordQuestion = reader.GetString(3);
  1141. string comment = string.Empty;
  1142. if (!reader.IsDBNull(4))
  1143. comment = reader.GetString(4);
  1144. bool isApproved = reader.GetBoolean(5);
  1145. bool isLockedOut = reader.GetBoolean(6);
  1146. DateTime creationDate = reader.GetDateTime(7);
  1147. DateTime lastLoginDate = new DateTime();
  1148. if (!reader.IsDBNull(8))
  1149. lastLoginDate = reader.GetDateTime(8);
  1150. DateTime lastActivityDate = reader.GetDateTime(9);
  1151. DateTime lastPasswordChangedDate = reader.GetDateTime(10);
  1152. DateTime lastLockedOutDate = new DateTime();
  1153. if (!reader.IsDBNull(11))
  1154. lastLockedOutDate = reader.GetDateTime(11);
  1155. MembershipUser u = new MembershipUser(this.Name,
  1156. username,
  1157. providerUserKey,
  1158. email,
  1159. passwordQuestion,
  1160. comment,
  1161. isApproved,
  1162. isLockedOut,
  1163. creationDate,
  1164. lastLoginDate,
  1165. lastActivityDate,
  1166. lastPasswordChangedDate,
  1167. lastLockedOutDate);
  1168. return u;
  1169. }
  1170. /// <summary>
  1171. /// Compares password values based on the MembershipPasswordFormat.
  1172. /// </summary>
  1173. /// <param name="password"></param>
  1174. /// <param name="dbpassword"></param>
  1175. /// <returns></returns>
  1176. bool CheckPassword(string password, string dbpassword)
  1177. {
  1178. string pass1 = password;
  1179. string pass2 = dbpassword;
  1180. switch (PasswordFormat)
  1181. {
  1182. case MembershipPasswordFormat.Encrypted:
  1183. pass2 = UnEncodePassword(dbpassword);
  1184. break;
  1185. case MembershipPasswordFormat.Hashed:
  1186. pass1 = EncodePassword(password);
  1187. break;
  1188. default:
  1189. break;
  1190. }
  1191. if (pass1.Equals(pass2))
  1192. return true;
  1193. else
  1194. return false;
  1195. }
  1196. /// <summary>
  1197. /// Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.
  1198. /// </summary>
  1199. /// <param name="password"></param>
  1200. /// <returns></returns>
  1201. string EncodePassword(string password)
  1202. {
  1203. if (string.IsNullOrEmpty(password))
  1204. return password;
  1205. string encodedPassword = password;
  1206. switch (PasswordFormat)
  1207. {
  1208. case MembershipPasswordFormat.Clear:
  1209. break;
  1210. case MembershipPasswordFormat.Encrypted:
  1211. encodedPassword = Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
  1212. break;
  1213. case MembershipPasswordFormat.Hashed:
  1214. HMACSHA1 hash = new HMACSHA1();
  1215. if (machineKeyIsAutoGenerated)
  1216. hash.Key = MachineKeySection.Config.GetValidationKey ();
  1217. else
  1218. hash.Key = HexToByte(m_MachineKey.ValidationKey);
  1219. encodedPassword = Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
  1220. break;
  1221. default:
  1222. throw new ProviderException(Properties.Resources.ErrPwFormatNotSupported);
  1223. }
  1224. return encodedPassword;
  1225. }
  1226. /// <summary>
  1227. /// Decrypts or leaves the password clear based on the PasswordFormat.
  1228. /// </summary>
  1229. /// <param name="encodedPassword"></param>
  1230. /// <returns></returns>
  1231. string UnEncodePassword(string encodedPassword)
  1232. {
  1233. string password = encodedPassword;
  1234. switch (PasswordFormat)
  1235. {
  1236. case MembershipPasswordFormat.Clear:
  1237. break;
  1238. case MembershipPasswordFormat.Encrypted:
  1239. password = Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
  1240. break;
  1241. case MembershipPasswordFormat.Hashed:
  1242. throw new ProviderException(Properties.Resources.ErrCantDecodeHashedPw);
  1243. default:
  1244. throw new ProviderException(Properties.Resources.ErrPwFormatNotSupported);
  1245. }
  1246. return password;
  1247. }
  1248. /// <summary>
  1249. /// Converts a hexadecimal string to a byte array. Used to convert encryption
  1250. /// key values from the configuration.
  1251. /// </summary>
  1252. /// <param name="hexString"></param>
  1253. /// <returns></returns>
  1254. byte[] HexToByte(string hexString)
  1255. {
  1256. byte[] returnBytes = new byte[hexString.Length / 2];
  1257. for (int i = 0; i < returnBytes.Length; i++)
  1258. returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
  1259. return returnBytes;
  1260. }
  1261. /// <summary>
  1262. /// A helper method that performs the checks and updates associated with
  1263. /// password failure tracking.
  1264. /// </summary>
  1265. /// <param name="username"></param>
  1266. /// <param name="failType"></param>
  1267. void UpdateFailureCount(string username, FailureType failType)
  1268. {
  1269. DateTime windowStart = new DateTime();
  1270. int failureCount = 0;
  1271. using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString))
  1272. {
  1273. // Fetch user data from database
  1274. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  1275. {
  1276. dbCommand.CommandText = string.Format("SELECT \"FailedPasswordAttemptCount\", \"FailedPasswordAttemptWindowStart\", \"FailedPasswordAnswerAttemptCount\", \"FailedPasswordAnswerAttemptWindowStart\" FROM \"{0}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  1277. AddParameter (dbCommand,"@Username", username);
  1278. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  1279. try
  1280. {
  1281. dbConn.Open();
  1282. dbCommand.Prepare();
  1283. using (SqliteDataReader reader = dbCommand.ExecuteReader())
  1284. {
  1285. if (reader.HasRows)
  1286. {
  1287. reader.Read();
  1288. if (failType.Equals(FailureType.Password))
  1289. {
  1290. failureCount = reader.GetInt32(0);
  1291. windowStart = reader.GetDateTime(1);
  1292. }
  1293. else if (failType.Equals(FailureType.PasswordAnswer))
  1294. {
  1295. failureCount = reader.GetInt32(2);
  1296. windowStart = reader.GetDateTime(3);
  1297. }
  1298. }
  1299. }
  1300. }
  1301. catch (SqliteException e)
  1302. {
  1303. Trace.WriteLine(e.ToString());
  1304. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  1305. }
  1306. finally
  1307. {
  1308. if (dbConn != null)
  1309. dbConn.Close();
  1310. }
  1311. }
  1312. // Calculate failture count and update database
  1313. using (SqliteCommand dbCommand = dbConn.CreateCommand())
  1314. {
  1315. DateTime windowEnd = windowStart.AddMinutes(m_PasswordAttemptWindow);
  1316. try
  1317. {
  1318. if (failureCount == 0 || DateTime.Now > windowEnd)
  1319. {
  1320. // First password failure or outside of PasswordAttemptWindow.
  1321. // Start a new password failure count from 1 and a new window starting now.
  1322. if (failType.Equals(FailureType.Password))
  1323. {
  1324. dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAttemptCount\" = @Count, \"FailedPasswordAttemptWindowStart\" = @WindowStart WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  1325. }
  1326. else if (failType.Equals(FailureType.PasswordAnswer))
  1327. {
  1328. dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAnswerAttemptCount\" = @Count, \"FailedPasswordAnswerAttemptWindowStart\" = @WindowStart WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  1329. }
  1330. AddParameter (dbCommand,"@Count", 1);
  1331. AddParameter (dbCommand,"@WindowStart", DateTime.Now);
  1332. AddParameter (dbCommand,"@Username", username);
  1333. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  1334. if (dbCommand.ExecuteNonQuery() < 0)
  1335. throw new ProviderException(Properties.Resources.ErrCantUpdateFailtureCountAndWindowStart);
  1336. }
  1337. else
  1338. {
  1339. failureCount++;
  1340. if (failureCount >= m_MaxInvalidPasswordAttempts)
  1341. {
  1342. // Password attempts have exceeded the failure threshold. Lock out
  1343. // the user.
  1344. dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"IsLockedOut\" = @IsLockedOut, \"LastLockedOutDate\" = @LastLockedOutDate WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  1345. AddParameter (dbCommand,"@IsLockedOut", true);
  1346. AddParameter (dbCommand,"@LastLockedOutDate", DateTime.Now);
  1347. AddParameter (dbCommand,"@Username", username);
  1348. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  1349. if (dbCommand.ExecuteNonQuery() < 0)
  1350. throw new ProviderException(string.Format(Properties.Resources.ErrCantLogoutUser, username));
  1351. }
  1352. else
  1353. {
  1354. // Password attempts have not exceeded the failure threshold. Update
  1355. // the failure counts. Leave the window the same.
  1356. if (failType.Equals(FailureType.Password))
  1357. {
  1358. dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAttemptCount\" = @Count WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  1359. }
  1360. else if (failType.Equals(FailureType.PasswordAnswer))
  1361. {
  1362. dbCommand.CommandText = string.Format("UPDATE \"{0}\" SET \"FailedPasswordAnswerAttemptCount\" = @Count WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName", m_TableName);
  1363. }
  1364. AddParameter (dbCommand,"@Count", failureCount);
  1365. AddParameter (dbCommand,"@Username", username);
  1366. AddParameter (dbCommand,"@ApplicationName", m_ApplicationName);
  1367. if (dbCommand.ExecuteNonQuery() < 0)
  1368. throw new ProviderException(Properties.Resources.ErrCantUpdateFailtureCount);
  1369. }
  1370. }
  1371. }
  1372. catch (SqliteException e)
  1373. {
  1374. Trace.WriteLine(e.ToString());
  1375. throw new ProviderException(Properties.Resources.ErrOperationAborted);
  1376. }
  1377. finally
  1378. {
  1379. if (dbConn != null)
  1380. dbConn.Close();
  1381. }
  1382. }
  1383. }
  1384. }
  1385. enum FailureType
  1386. {
  1387. Password,
  1388. PasswordAnswer
  1389. }
  1390. #endregion
  1391. }
  1392. }
  1393. #endif