Test-DbaLoginPassword failed when the dictionary is too long

rferraton commented 1 year ago

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

WARNING: [07:46:05][Test-DbaLoginPassword] Failure | Incorrect syntax near ' UNION SELECT '. The identifier that starts with ''(-' UNION SELECT 'janine' UNION SELECT 'hitman' UNION SELECT 'computer1' UNION SELECT 'chon' is too long. Maximum length is 128. Unclosed quotation mark after the character string ''(-' UNION SELECT 'janine' UNION SELECT 'hitman' UNION SELECT 'computer1' UNION SELECT 'chonc'.

Steps to Reproduce

Download a long list for password check. I used this one : https://github.com/danielmiessler/SecLists/blob/master/Passwords/richelieu-french-top5000.txt

$richelieufr5000= Get-Content -Path "D:\OpenData\Password\richelieu-french-top5000.txt"
$MyServerList = @("localhost")
$MyServersList | Get-DbaLogin | Test-DbaLoginPassword -Dictionary $richelieufr5000

Please confirm that you are running the most recent version of dbatools

2.1.0

Other details or mentions

I suggest using VALUES instead of SELECT and loop with (val UNION ALL) in the Test-DbaLoginPassword.ps1

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name Value

PSVersion 7.3.6 PSEdition Core GitCommitId 7.3.6 OS Microsoft Windows 10.0.19045 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0

SQL Server Edition and Build number

Microsoft SQL Server 2022 (RTM-CU5) (KB5026806) - 16.0.4045.3 (X64) May 26 2023 12:52:08 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19045: ) (Hypervisor)

.NET Framework Version

.NET 7.0.9

HCRitter commented 1 year ago

It appears that the issue stems from unescaped characters within the password list, specifically characters like '.

Upon further investigation, I've identified that just before the error occurs, the password appears as follows: &é"'(-.

To address this issue, I recommend implementing a solution to escape these characters and potentially others that may cause similar problems. One way to tackle this is by creating a helper function designed to sanitize input data, preventing these types of errors from occurring in the future.

By incorporating such a function into your codebase, you can ensure that passwords, and possibly other user inputs, are properly sanitized to avoid issues related to special characters.

Let me know if you need assistance with implementing this helper function or have any further questions regarding this issue.

niphlod commented 1 year ago

urgh, yes, definitely it needs to use a parametrized query (and while we're there, batching, as I don't think we can then support zillions of values in one go)

dataplat / dbatools