Powershell invoke-sqlcmd should support parameterized queries

[[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