title: Escaping strings containing single quotes in PowerShell ready for SQL query
body: <p>I am trying to run the following query, which takes someone's name and attempts to insert it into an SQL Server database table.</p>
  
  <pre><code>$name = "Ronnie O'Sullivan"
  
  $dataSource = "127.0.0.1"
  $database = "Danny"
  $connectionString = "Server=$dataSource;Database=$database;Integrated Security=True;"
  
  $connection = New-Object System.Data.SqlClient.SqlConnection
  $connection.ConnectionString = $connectionString
  $connection.Open()
  
  $query = "INSERT INTO People(name) VALUES('$name')"
  
  $command = $connection.CreateCommand()
  $command.CommandText = $query
  $command.ExecuteNonQuery()
  
  $connection.Close()
  </code></pre>
  
  <p>The problem I am facing is that the single quote is causing an issue in my query. The query is being executed as</p>
  
  <pre><code>INSERT INTO People(name) VALUES('Ronnie O'Sullivan')
  </code></pre>
  
  <p>which causes an SQL syntax error.</p>
  
  <p>My question is how do I escape my $name variable so that it renders on the SQL side.</p>
  
  <p>One solution is to do a find and replace on my $name variable, find: ' replace: ''</p>
  
  <pre><code>$name.Replace("'", "''")
  </code></pre>
  
  <p>Is there a more elegant solution out there, or a function that I can't seem to find?</p>
  
  <p>Thank you.</p>