Powershell API request issue - very strange

Question

I'm pulling nutrient reports for various foods from the USDA food API. Im using a powershell script for the request and the USDA documentation states a maximum of 1500 records per request. I can pull the values all the way up to max=164 but as soon as I try and change it to 165 I get an incorrect syntax error near 'S'. Any clue as to what might be causing this? Code and errors below

PS:

    Function Add-APIData ($server, $database, $text)
    {

$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "SERVER=$server;DATABASE=$database;Integrated Security=true"

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $scon
$cmd.CommandText = $text
$cmd.CommandTimeout = 0

$scon.Open()
$cmd.ExecuteNonQuery()
$scon.Close()
$cmd.Dispose()
$scon.Dispose()
    }
    //this is where the max value is stored 
    [string]$webstring = "https://api.nal.usda.gov/ndb/nutrients/?format=json&max=164&api_key=CLJnYzvrhMBcFtoQ4hohIL4Scs3tiRpOFXS7UhHM&nutrients=205&nutrients=204&nutrients=208&nutrients=269&fg=0100"
    $webget = New-Object System.Net.WebClient
    $result = $webget.DownloadString($webstring)

$result = ConvertFrom-Json $result
$add = @()

foreach ($r in $result.report.foods){

        $add += "INSERT INTO BRUH VALUES ('" + $r.ndbno + "','" + $r.name + "')"


}

    Add-APIData -server "NATHAN\SQLEXPRESS" -database "Lab2" -text $add

ERRORS:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near 'S'. Unclosed quotation mark after the character string ')'." At line:12 char:5 + $cmd.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException

alroc alroc · Accepted Answer · 2017-10-30T11:36:06

Most likely, there is an apostrophe (single quote) in your data. Because you're concatenating strings into your INSERT statement, these will not be parsed properly by the database and this is one of the leading causes of SQL injection attacks!

If you output your query to console and then paste it into SSMS, the syntax highlighting will show this to you quickly.

A second (remote) possibility is that you're exceeding the maximum allowed length for a query.

Change your code to use Parameterized Queries (example) and execute a single INSERT at a time, not a ridiculously large batch all at once.

Powershell API request issue - very strange

1 Answers