August 20, 2024 at 3:17 pm
Apologies as this has probably been asked. I searched but couldn't find it.
I have the following code:
if ([string]::IsNullOrEmpty($OrgID) -eq $true)
{
write-host "Null found"
$OrgID=[DBNull]::Value
}
write-host "insert into dbo.Org(id,code) values ($OrgID,'$orgcode')"
$sqlcomODS.CommandText="insert into dbo.Org(id,code) values ($OrgID,'$orgcode')"
$sqlcomODS.ExecuteNonQuery() | Out-Null
dbo.org.id is an int. I'm trying to deal with the fact that sometimes $orgid is null or blank and convert it to a null.
For an empty/null value the output contains "Null found" so the if statement appears to work however the second write-host gives
insert into dbo.Org(id,code) values (,'ABCD')
and therefore generates a SQL syntax error.
Is there a way of inserting nulls or do I have to switch to something like Write-DataTable?
I know I could use an if statement to decide whether to include the field in the sql statement but I'm looking for something neater especially as I have several fields to manage.
Thanks
August 20, 2024 at 8:18 pm
replace your code with
if ([string]::IsNullOrEmpty($OrgID) -eq $true)
{
write-host "Null found"
#$OrgID=[DBNull]::Value # not needed neither possible if $OrgID was defined as a int in PS
}
write-host "insert into dbo.Org(id,code) values ($OrgID,'$orgcode')"
# replace original line with one of the 2 options beelow
#$sqlcomODS.CommandText="insert into dbo.Org(id,code) values ($OrgID,'$orgcode')"
# any PS version
#$sqlcomODS.CommandText="insert into dbo.Org(id,code) values ($(if([string]::IsNullOrEmpty($OrgID)){"NULL"}else{$OrgID}),'$orgcode')"
# PS 7 or higher
#$sqlcomODS.CommandText="insert into dbo.Org(id,code) values ($([string]::IsNullOrEmpty($OrgID) ? "NULL" : $OrgID),'$orgcode')"
$sqlcomODS.ExecuteNonQuery() | Out-Null
but you should instead modify your code to use parameters instead of variable replacement - that way not only code can get better structured as it also prevents SQL Injection.
plenty of examples on net - one here
August 20, 2024 at 8:19 pm
dup post
August 21, 2024 at 11:28 am
Thanks for your help.
The code you provided solved the issue and you are right I need to change it due to the SQL injection risk which I will do.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy