请建议输入密码的sql查询，不要接受连续的复制chrcters [英] please suggest sql query for input password whcich does not contatin consecutuive reprating chrcters

问题描述

大家好，



请建议输入密码的sql查询不要接受连续的复制chrcters

解决方案

为什么要使用SQL查询来执行此操作？

这意味着将人类可读的文本字符串密码传递给SQL，这是一个安全风险以及愚蠢的 - 密码应该是在他们进入SQL之前就已经很好了：密码存储：如何做到。 [ ^ ]



此外，为什么两个连续的字符不一样：我经常使用GUID值作为密码，它们确实包含重复的字符 - 这不会减少最轻微的安全级别！



在演示软件中进行验证，并仅发送有效（安全）数据到SQL。

看看例子：

  DECLARE   @ string   VARCHAR （ 30 ）= < span class =code-string>'  rahkkkff' 
 
;  WITH  CTE  AS  
（
  SELECT   1   AS  LetterPosition， CONVERT （ VARCHAR （ 1 ）， LEFT （ @string ， 1 ）） AS  Letter，正确（ @ string ，LEN（ @ string ） -  1） AS 剩余，CHARINDEX（REPLICATE（ LEFT （ @ string ，< span class =code-digit> 1 ）， 3 ）， @ string ，  1 ） AS  FoundedAt 
  WHERE  LEN（ @ string ）> 1 
  UNION   ALL  
  SELECT  LetterPosition +  1   AS  LetterPosition， CONVERT （ VARCHAR （ 1 ）， LEFT （剩余， 1 ）） AS  Letter， RIGHT （剩余，LEN（剩余）-1） AS 剩余，CHARINDEX（REPLICATE（ LEFT （剩余， 1 ）， 3 ），剩余， 1 ） AS  FoundedAt 
  FROM  CTE 
  WHERE  LEN（剩余）> 1 
  UNION   ALL  
  SELECT  LetterPosition +1  AS  LetterPosition， CONVERT （ VARCHAR （ 1 ），余数） AS  Letter， NULL   AS 剩余，CHARINDEX（REPLICATE（ LEFT （剩余， 1 ）， 3 ），Remaind呃， 1 ） AS  FoundedAt 
  FROM  CTE 
  WHERE  LEN（剩余）= 1 
）
  SELECT   @ string   AS  MyString，REPLICATE（Letter， 3 ） AS  ConsecutiveLetters，LetterPosition  AS  FoundedAt 
  FROM  CTE 
  WHERE  FoundedAt = 1 

结果：

 rahkkkff kkk 4 

以上查询用于检查3个连续字母的字符串，但是 ...

你写道：查询输入密码，不接受连续的复制chrcters ，这意味着您将密码存储为纯文本。这是坏主意！

请参阅：

http://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database [ ^ ]

初学者指导存储密码的安全方式 [ ^ ]

hello guys,

please suggest sql query for input password whcich does not contatin consecutuive reprating chrcters

解决方案

Why would you want to use an SQL query to do this?
That would mean passing a human readable text string password to SQL, which is a security risk as well as silly - passwords should be hashed well before they get to SQL: Password Storage: How to do it.[^]

And besides, why should two consecutive characters not be the same: I frequently use GUID values as passwords, and they do contain repeating characters - which does not reduce the security level in the slightest!

Do validation in your presentation software, and only send valid (secure) data to SQL.

Have a look at example:

DECLARE @string VARCHAR(30) = 'rahkkkff'

;WITH CTE AS
(
	SELECT 1 AS LetterPosition, CONVERT(VARCHAR(1),LEFT(@string, 1)) AS Letter, RIGHT(@string,LEN(@string)-1) AS Remainder, CHARINDEX(REPLICATE(LEFT(@string, 1),3), @string, 1) AS FoundedAt
	WHERE LEN(@string)>1
	UNION ALL
	SELECT LetterPosition + 1 AS LetterPosition, CONVERT(VARCHAR(1),LEFT(Remainder, 1)) AS Letter, RIGHT(Remainder,LEN(Remainder)-1) AS Remainder, CHARINDEX(REPLICATE(LEFT(Remainder, 1),3), Remainder, 1) AS FoundedAt
	FROM CTE
	WHERE LEN(Remainder)>1
	UNION ALL
	SELECT LetterPosition +1 AS LetterPosition, CONVERT(VARCHAR(1),Remainder) AS Letter, NULL AS Remainder, CHARINDEX(REPLICATE(LEFT(Remainder, 1),3), Remainder, 1) AS FoundedAt
	FROM CTE
	WHERE LEN(Remainder)=1
)
SELECT @string AS MyString, REPLICATE(Letter,3) AS ConsecutiveLetters, LetterPosition AS FoundedAt
FROM CTE
WHERE FoundedAt=1

Result:

rahkkkff	kkk	4

Above query is used to check string for 3 consecutive letters, BUT...
You wrote: "query for input password whcich does not contatin consecutuive reprating chrcters", which means that you store passwords as a plain text. It's BAD idea!
Please, see:
http://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database[^]
Beginners guide to a secure way of storing passwords[^]

这篇关于请建议输入密码的sql查询，不要接受连续的复制chrcters的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！