Powershell sql queries

Question

I am new to powershell and trying to built a script which can accept username and fetch corresponding userID from table(SUser), based on which i should delete that particular user from that table. Below is the code, kindly correct in case of any syntax errors cause i am not able to find any error and

I am not able to fetch values properly. Some times the query doesnt fetch values and some times the results on first run are displayed on the second run.

clear
$servername = "Someserver"
$databasename = "someDB"
$c = Get-Credential

$cmd1 = "select UserID from SUser where UserName='$Name'"
$cmd2 = "delete from SUser where UserID = '$a'"

$connectionString = [string]::Format( "server={0};database={1};uid={2};pwd=    {3};Integrated     Security=True;", "$servername", "$databasename",$c.username,$c.GetNetworkCredential    ().password)

$conn = New-Object system.Data.SqlClient.SqlConnection
$conn.connectionstring = $connectionString
$conn.open()
switch ($conn.State)
{
"Open"  
    { 
        $SqlCmd1 = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd1.CommandText = $cmd1
        $SqlCmd1.Connection = $Conn

        $Name = Read-host "Enter UserName (One user name at a time):"
        write-host "UserName Entered:" $Name
        $usrname = $SqlCmd1.ExecuteScalar()
        $a = $usrname
        write-host "User Id for Username $Name :"$a


        $SqlCmd2 = New-Object System.Data.SqlClient.SqlCommand    
        $SqlCmd2.CommandText = $cmd2
        $SqlCmd2.Connection = $Conn

        $usrid = $SqlCmd2.ExecuteScalar()

     }
Default 
     { Write-Host "The connection is $($conn.State)"; 
     }
 }
$conn.close()

Thanks, Hari

What do you get when you add a write-host $SqlCmd1.CommandText and write-host $SqlCmd2.CommandText before you execute them? — Ocaso Protal
– Ocaso Protal, Commented Dec 3, 2013 at 11:20
Actually I was using another select statement instead of a delete for $cmd2 at top for testing $cmd2 = "select UserName from SUser where UserID='$a'" I am getting the below lines in the ouput pane: select UserID from SUser where UserName='' select UserName from SUser where UserID='' — hari061204
– hari061204, Commented Dec 3, 2013 at 11:28

Ocaso Protal · Accepted Answer · 2013-12-03 12:59:36Z

1

The problem here is that you asign $cmd1 when the variable $Name is not yet set. Variable expansion does not work like a templateing mechanism in Powershell. So you have to change

$SqlCmd1.CommandText = $cmd1

to

$SqlCmd1.CommandText = "select UserID from SUser where UserName='$Name'"

in your script (the same applies for $SqlCmd1).

BTW: Your script is prone to SQL Injection!

answered Dec 3, 2013 at 12:59

Ocaso Protal

20.4k8 gold badges80 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

hari061204 Over a year ago

Thanks a lot. Works fine :) Its just an internal purpose script.

alroc Over a year ago

It doesn't matter what the "purpose" of the script is. Always code defensively.

hari061204 Over a year ago

Hi, The above code will ask for User Credentials for loging in. But even with improper(incorrect) credentials, i am able to log into DB. and execute quieries Any problem with connection string ?

Ocaso Protal Over a year ago

Could you please make that a new question, because I cannot (currently) reproduce your problem. Maybe with some simplified code?

hari061204 Over a year ago

Here is the link for my new question : stackoverflow.com/questions/20403707/…

Collectives™ on Stack Overflow

Powershell sql queries

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related