SQL ExecuteNonQuery Error in Powershell Query with Boolean

Question

I'm getting an error in my powershell script that is the following:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Invalid column name 'False'.

function InsertData {
    Param (
    [string]$username,
    [string]$fullname,
    [string]$email,
    [string]$phone
    )
    $DBServer = "SERVERNAME"
    $DBName = "Tool"
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=False;User Id = User;password = password"
    $sql = "INSERT INTO Employee (LocationId,FullName,username,email,phone,EquipId,SystemDetailId,migrationdate,UAT,bdpaccess) VALUES ('" + $location + "','" + $fullname + "','" + $username + "','" + $email + "','" + $phone + "',5,4," + $migrationdate + ",False,False)"

    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $sqlCommand.Connection = $sqlConnection

    $sqlCommand.CommandText = $sql

    write-host $sql
    $sqlConnection.Open()
    $sqlCommand.ExecuteNonQuery()
    $sqlConnection.Close()
}

I'm apparently doing something wrong where I'm typing "False,False" as part of the statement. I tried all caps, lowercase, 0 and 1, nothing works. When I write it to the powershell command window using Write-Host $sql, it looks fine.

If the SQL data type is bit, specify zero for the literal. Consider a parameterized query. — Dan Guzman
– Dan Guzman, Commented Jun 26, 2017 at 18:52
How is this called? It looks like an sql injection nightmare to me. Even benign data like someone with an apostrophe in their full name ("O'brien", Bobby Tables) will kill this dead. — Joel Coehoorn
– Joel Coehoorn, Commented Jun 26, 2017 at 18:54
I tried parameters but they didn't work, they got too many errors. — Nathan McKaskle
– Nathan McKaskle, Commented Jun 26, 2017 at 19:11
I'm not worried about injection, this isn't public. Nobody's inserting anything into any fields. It's powershell. I'm calling it by typing it from the command prompt. — Nathan McKaskle
– Nathan McKaskle, Commented Jun 26, 2017 at 19:11

Joel Coehoorn · Accepted Answer · 2017-06-26 18:57:23Z

2

Sql Server (like most other DBs) does not have a real boolean type. There is no False. It does have a bit type with values 0 (false) and 1 (true). I've also seen char(1) (ie: 'Y' and 'N') and DateTime types (where NULL is false and any other value is the date on which the field became true) used as popular alternatives.

While I'm here, that code looks scary. Sql Injection is a real thing, and it's a big deal. Even benign data like some with the last name "O'Brien" could throw this query off in a big way. You need to look into parameterized queries. At minimum, looking more at the query text, I suspect the $migrationdate value needs single quotes around it.

answered Jun 26, 2017 at 18:57

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

6 Comments

Nathan McKaskle Over a year ago

tried parameters but they didn't work, they got too many errors. I'm not worried about injection, this isn't public. Nobody's inserting anything into any fields. It's powershell. I'm calling it by typing it from the command prompt.

Nathan McKaskle Over a year ago

Also I tried quotes around $migrationdate and it didn't like that. The string says 2/20/2017.

Joel Coehoorn Over a year ago

Do me a favor, and don't stop with this until you can correctly insert a name with an apostrophe. It's not always about malicious injection; perfectly normal data can break this, too, and parameterized queries are the easiest and best solution.

Nathan McKaskle Over a year ago

Parameterized queries gave me this error: The splatting operator '@' cannot be used to reference variables in an expression. '@dt' can be used only as an argument to a command. when trying to use sqlCommand.Parameters.Add(@dt, $migrationdate) or literally anything else I tried to add as a parameter.

Nathan McKaskle Over a year ago

I fixed the errors I was getting with parameters, apparently 0 and 1 as booleans are the only way to get it to work.

|

Collectives™ on Stack Overflow

SQL ExecuteNonQuery Error in Powershell Query with Boolean

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related