title: Parameterized SQL Query Incorrect Syntax
body: <p>The way I understand parameterized SQL queries is the way to avoid escape character errors. Yet I still encounter this problem.</p>
  
  <pre><code>try {
      Import-Module ActiveDirectory
  
      $abc = Get-ADUser -ResultSetSize 9998 -Properties employeeid,samaccountname,Department,physicalDeliveryOfficeName,mail,useraccountcontrol,telephonenumber,cn,title,mobile,company,description,manager
  
      $connection = New-Object System.Data.SqlClient.SqlConnection
      $connection.ConnectionString = "server=server.local;database=db;trusted_connection=true;"
      $connection.Open()
  
      $command = New-Object System.Data.SQLClient.SQLCommand
      $command.Connection = $connection
  
      #$command.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@department",[Data.SQLDBType]::VarChar, 250)))
      #$command.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@physicalDeliveryOfficeName",[Data.SQLDBType]::VarChar, 200)))
  
      foreach ($user in $abc) {
          $command.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@physicalDeliveryOfficeName",[Data.SQLDBType]::VarChar, 200))).value = $user.physicalDeliveryOfficeName
          $command.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@department",[Data.SQLDBType]::VarChar, 250))).value = $user.Department
          #$command.Parameters['@department'].value = $user.Department
          #$command.Parameters['@physicalDeliveryOfficeName'].value = $user.physicalDeliveryOfficeName
  
          #if (!$user.department)                 { $Command.Parameters['@department'].value = [System.DBNull]::Value  }
          #if (!$user.physicalDeliveryOfficeName) { $Command.Parameters['@physicalDeliveryOfficeName'].value = [System.DBNull]::Value  }
  
          $insert = "INSERT INTO [Database].[ad].[UserAccountsT] (employeeid,samaccountname,distinguishedName,givenname,sn,title,department,physicaldeliveryofficename,email,telephoneNumber,mobile,company,description,useraccountcontrol,cn,manager) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')" -f $user.employeeid,$user.samaccountname,$user.DistinguishedName,$user.GivenName,$user.Surname,$user.title,$user.Department,$user.physicalDeliveryOfficeName,$user.mail,$user.telephonenumber,$user.mobile,$user.company,$user.description,$user.userAccountControl,$user.cn,$user.manager
          $command.CommandText = $insert
          $command.ExecuteNonQuery() &gt; $null
          $command.Parameters.Clear()
      }
  } catch {
      Write-Host Everything goes wrong at $_ for $user at $user.physicalDeliveryOfficeName $user.Department !!!
  } finally {
      $connection.Close()
  }
  </code></pre>
  
  <p>But when there is a <code>'</code> in the name I still get an error:</p>
  
  <blockquote>
    <p>Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near 's'.</p>
  </blockquote>
  
  <p><code>$user.physicalDeliveryOfficeName</code> value is "Park 's avonds".</p>