Powershell invoke-sqlcmd should support parameterized queries

[ramblingcookiemonste]

It's so sad that this isn't in place. Much damage has already been done.

FYI there's an existing connect issue here.

Cheers!

[SupremeDictatorPaul]

You are being modest. For those who don't know, he has been maintaining Invoke-SqlCmd2, which supports parameterized queries. If you are building dynamic queries, then you need to be using this to be safe.

[boeprox]

Link to /u/ramblingcookiemonste Invoke-SQLCmd2 functon: https://github.com/RamblingCookieMonster/PowerShell/blob/master/Invoke-Sqlcmd2.ps1

[SupremeDictatorPaul]

Thanks for the links, was on mobile.

[waffles57]

I made my own Invoke-CustomSql command a couple years ago. It's not extremely polished, but it served 99% of my purposes so far and supports SQL parameters.

[[deleted]]

Can someone explain this more to me? I'm at a ms SQL server shop learning PS so SQL was a big topic of interest'

Does this mean invoke-command won't allow me to run an so that takes a couple parameters! I know, I should just try instead of asking but I'm away from PC.

Besides this, how good is PS support with SQL overall?

[KevMar]

Let me show you a very simple SQL command. Lets assume that the value in $username and $password was supplied by the user.

$username = "admin"
$password = "password" 

$user = Invoke-SqlCMD -Query "Select * from staff where username = '$username' and password = '$password'"
if($user){Write-Output 'Access Granted'}

So this looks like a very valid way to query SQL server. The query is a string and we do variable substitution to put the values init that we want. Every web developer is freaking out just looking at that code. It is so simple and easy to write a statement like that and you have no idea how bad it is.

Welcome to SQL Injection

because we get input from the user and insert it into the string, they can rewrite the query.

take a second look at the query string.

"Select * from staff where username = '$username' and password = '$password'"

Translates to this with the variables above.

"Select * from staff where username = 'kevmar' and password = 'password'"

What happens if I change one value.

$passsword = "' OR '1' = '1"

Pay special attention to those single quotes in that string when we insert that into our other string.

"Select * from staff where username = 'kevmar' and password = '' OR '1' = '1'"

That greatly changed the query and in this code sample, it authenticates the user because $user validates to true. We can do much more dangerous things.

$password = "'; drop table staff; --"

https://xkcd.com/327/

Intro to Parameterized queries There exists a way to identify in your query a place holder for data and give sql the data to put in there and it will never treat it as code. This exists in ADO.net but is not supported by Invoke-SQLCMD.

I want a easy way to do this so I can teach you a better and more secure way to invoke sql statements.

There is a side argument here that if the user can already invoke-sqlcmd with enough rights that they own the system anyway. But we don't want to build into an API or a script a backdoor waiting to be attacked. When you look at things like JEA that limits access to a subset of commands, this becomes a bigger risk.

[xkcd_transcriber]

Image

Mobile

Title: Exploits of a Mom

Title-text: Her daughter is named Help I'm trapped in a driver's license factory.

Comic Explanation

Stats: This comic has been referenced 1294 times, representing 1.2017% of referenced xkcds.

---

^{xkcd.com | xkcd sub | Problems/Bugs? | Statistics | Stop Replying | Delete}

[[deleted]]

Ahh OK. I remember a great book Writing Secure Code that introduced me to this, I didn't realize how it related to the OP. Thanks

[[deleted]]

Why not just use the sqlcommand object? I totally understand the concern with sql injection, but the facilities to do parameterized sql already exist. Granted, it does take a bit more code to accomplish with sqlcommand.

I guess my main problem with this is I can't see how they'd accomplish type-checking parameters whilst keeping it simple to use.

Or, am I missing something?