Экранирование одинарной кавычки в строке в Powershell перед передачей значения в SQL
У меня есть сценарий, который извлекает информацию, относящуюся к файлам в заданном пути к папке, а затем передает эти значения через модуль SQLPS и команду SQL INSERT INTO для заполнения моей таблицы базы данных.
Это работает нормально, за исключением нескольких случаев, когда путь к файлу или папке включает апостоф.
Соответствующий блок кода
$PathArowCount=0
$filelist = Get-ChildItem -Path $localPath -Recurse | Where-Object {!($_.PSIsContainer)} | Select DirectoryName, Name, Length, LastWriteTime
ForEach ($file in $filelist)
{
# Retrieve all values into variables. Value passed to the insert statement needs to be within single quotes, so escaping them out here so they're included later.
$insFolder = "`'$($file.DirectoryName)`'"
$insFile = "`'$($file.Name)`'"
$insLength = "`'$($file.Length)`'"
$insLastWrite = "`'$($file.LastWriteTime)`'"
write-host "$insFolder`n$insFile`n$insLength`n$insLastWrite`n---"
# Generate the SQL insert statement using the values above
$sqlCommand.CommandText = "INSERT INTO [$dbCompare].[dbo].[PathA] (FilePath,FileName,FileLength,LastWriteTime) VALUES ($insFolder,$insFile,$insLength,$insLastWrite)"
# Execute the SQL command while also incrementing the row count for use later
$PathArowCount = $PathArowCount + $sqlCommand.ExecuteNonQuery()
}
write-output "$(Get-Date) - $PathArowCount rows (eg files found) inserted into table PathA"
Где, как вы можете видеть, я уже устанавливаю входные значения $insFolder, $insFile, $insLength и $insLastWrite, которые передаются команде SQL, чтобы включать одинарные кавычки, поскольку это ожидается SQL.
Моя проблема заключается в том, что имя файла/папки включает апостроф/одинарную кавычку, например:
c:\Myfiles\Keith's Document.docx
Я получаю следующую ошибку из строки $sqlCommand.ExecuteNonQuery():
Exception calling "ExecuteNonQuery" with "0" argument(s): Incorrect syntax near 's'.
Unclosed quotation mark after the character string')'."
Строка Write-Host показывает мне, что в этот момент $insFile правильно содержит всю строку, как и ожидалось, с одинарными кавычками на обоих концах, например:
'Keith's Document.docx'
Но из-за ошибки SQL, по-видимому, видит переданное ему значение как
'Keith'
а затем ошибка из-за неожиданного аргумента:
s Document.docx'
Мне, очевидно, нужно найти и скрыть этот символ, где это уместно, и я, например, пробовал различные комбинации.
$insFile = "`'$($file.Name).Replace("`'","\`'")`'"
где я избегаю одинарных кавычек в значениях сравнения с помощью обратных кавычек и пытаюсь добавить \ к обратной кавычке, чтобы избежать этого символа, когда он достигает SQL, но каждая комбинация, которую я пробовал, вызывает ошибку Powershell, поэтому я не уверен, что волшебная комбинация.
вы должны использовать параметризованные запросы, это неправильный путь
Спасибо за толчок в правильном направлении @SantiagoSquarzon Сначала я думал, что это чисто с точки зрения безопасности ... но теперь, когда я нашел, как это сделать, и заработал, я знаю лучше. :)
Ответы 2
тл;др
Всегда предпочтительнее использовать параметризованный запрос , а не интерполяцию строк, чтобы избежать головной боли с цитированием и, что более важно, исключить любую возможность атаки SQL-инъекцией.
Если вы хотите придерживаться интерполяции строк, вы должны экранировать значение-внутреннее
'как'':$insFile = "'$($file.Name -replace "'", "''")'" # Alternative: $insFile = "'{0}'" -f ($file.Name -replace "'", "''")
Цитирование соображений:
Перспектива PowerShell в отношении строковых литералов:
Внутри
"..."- расширяемые (интерполирующие) строки -'символы. не требуют экранирования и могут использоваться как есть.Внутри
'...'- дословные строки - встроенные'символы., должны экранироваться как''- однако такие строки здесь не задействованы; см. ниже.
Перспектива T-SQL (где строковые литералы называются символьными строковыми константами):
'...'строки, встроенные в строки запроса, также требуют экранирования внутри строки'как''.(
"..."литералы по умолчанию не поддерживаются, и ни одна из форм не интерполируется)
Большое спасибо @SantiagoSquarzon за то, что указал мне то, что я даже не осознавал в то время, было правильным направлением. Получил свой скрипт с использованием параметризованных запросов (как только я узнал, как), думая, что это улучшит безопасность, и был приятно удивлен, осознав (задним числом это очевидно), что он также сводит на нет проблемы с escape-символами.
Для всех, кто заинтересован, окончательный рабочий код, с которым я столкнулся,
$PathArowCount=0
$filelist = Get-ChildItem -Path $localPath -File -Recurse | Select DirectoryName, Name, Length, LastWriteTime
ForEach ($file in $filelist)
{
$sqlCommand.CommandText = "INSERT INTO [$dbCompare].[dbo].[PathA] (FilePath,FileName,FileLength,LastWriteTime) VALUES (@Folder,@File,@Length,@LastWrite)"
$sqlCommand.Parameters.Clear()
$sqlCommand.Parameters.Add("@folder", $($file.DirectoryName)) | Out-Null
$sqlCommand.Parameters.Add("@file", $($file.Name)) | Out-Null
$sqlCommand.Parameters.Add("@length", $($file.Length)) | Out-Null
$sqlCommand.Parameters.Add("@lastwrite", $($file.LastWriteTime)) | Out-Null
$PathArowCount = $PathArowCount + $sqlCommand.ExecuteNonQuery()
}
write-output "$PathArowCount rows (eg files found) inserted into table PathA"
Обратите внимание, что из комментариев ниже видно, что используемый здесь метод .Add( устарел, поэтому он может не работать в более новых версиях. Подтверждено, что он работает с Powershell 5.1 в Windows Server 2012 с SQL 2012 и Windows Server 2012 R2 с SQL 2016.
Интересно, что еще до того, как я дошел до этого момента и еще пытался разобраться с экранирующими символами, я обнаружил, что в то время как
$insFile = "`'$($file.Name).Replace("'","''")`'"
выдает ошибку Powershell, разбивая ее на две строки, подобные этой
$insFileTmp = $($file.Name).Replace("'","''")
$insFile = "`'$insFileTmp`'"
работал нормально и делал то, что я искал ранее (до того, как узнал лучше!).
вы также можете использовать System.Data.SqlClient.SqlConnection в качестве аргумента для вашего System.Data.SqlClient.SqlCommand :) таким образом, вам не нужно жестко кодировать БД, в которую вы пишете, в строке запроса
Прохладный. В этом случае весь сценарий фактически подключается к двум разным базам данных на одном сервере, и это другая база данных, используемая в исходной строке подключения. Кроме того, скрипт создает базу данных $dbcompare с уникальным именем и таблицу PathA. Таким образом, при чтении моего кода становится немного проще отслеживать, на что и где ссылаются. :)
Приятно видеть параметризованное решение, но я озадачен тем, что что-то вроде $sqlCommand.Parameters.Add("@file", $file.Name) работает, потому что перегрузки SqlParameterCollection.Add() , похоже, не поддерживают такой вызов с двумя аргументами. $sqlCommand не относится к типу SqlCommand?
Приятно видеть параметризованное решение, но я озадачен тем, что что-то вроде $sqlCommand.Parameters.Add("@file", $file.Name) работает, потому что перегрузки SqlParameterCollection.Add() , похоже, не поддерживают такой вызов с двумя аргументами. $sqlCommand не относится к типу SqlCommand?
Что касается проблемы с экранированием: обратите внимание, что вне "..." вам никогда не нужно заключать ссылки на переменные в $(...) (и даже внутри "..." вам это нужно только для выражений, а не для простых ссылок на переменные). Таким образом, $sqlCommand.Parameters.Add("@file", $($file.Name)) можно упростить, например, до $sqlCommand.Parameters.Add("@file", $file.Name). И наоборот, ваша попытка интерполяции строк не удалась, потому что вызов .Replace() должен быть внутри встроенного $(...): $insFile = "'$($file.Name.Replace("'","''"))'" — также обратите внимание, что обратные кавычки (`) не нужны.
Да, я понимаю, что $($..) часто бывает излишним, у меня просто вошло в привычку после того, как меня поймали достаточно раз там, где это было необходимо, тогда как делать это, когда это не нужно, все еще работает. Раньше были необходимы обратные кавычки, поэтому то, что попало в команду SQL, все еще было окружено одинарными кавычками... но затем оно стало избыточным, как только все было параметризовано.
@ mklement0 Честно говоря, понятия не имею о синтаксисе параметров ... просто погуглил и нашел сообщение (вероятно, здесь) с кем-то, перечисляющим этот синтаксис, поэтому скопировал его, и это сработало. :) Странно думать, что после 10 лет написания различных вещей в Powershell и 20 лет администрирования SQL (хотя и в меньшей степени) я впервые разговариваю друг с другом.
Спасибо, @Кит. Любопытный синтаксис параметра re; ответ, на который я ссылаюсь из своего ответа, использует перегрузку с 3 параметрами с последующей настройкой свойства .Value. Очевидно, таинственная перегрузка с двумя параметрами более удобна, но она основана на определении типа данных параметра, что не всегда может работать так, как предполагалось. Re ': как показывает правильная форма $insFile = ... в моем предыдущем комментарии, вы никогда не должны `-убегать ' внутри "..." (хотя это не наносит вреда).
@ mklement0 они, вероятно, имели в виду .AddWithValue не .Add вероятно. опечатка в ответе
@mklement0 Я взял с stackoverflow.com/questions/16734039/… и это сработало, но, посмотрев в другом месте, теперь я понимаю, что вы имеете в виду. Вероятно, будет настроено использовать стандартный метод, а не полагаться на удачу с выводом типов данных. Примечания здесь Learn.Microsoft.com/en-us/dotnet/api/… может объяснить, предлагает синтаксис, который я использовал, работает, но устарел + я запускаю скрипт с PS5.1 на сервере 2012, поэтому может не работать на более новых .
Вместо экранирования используйте две одинарные кавычки: $insFile = 'c:\Myfiles\Keith's Document.docx' $insFile = $insFile.Replace("'","''") $insFile