An SQL parameter supplies a value separately from a named placeholder in a SQL statement. In PowerShell, write the placeholder with @, then pass its value through the SqlParameter hashtable to Invoke-DbaQuery. section 7.4.1
Parameterization improves dynamic queries by:
- Checking data types and converting values such as DateTime from PowerShell to SQL format. section 7.4.1
- Escaping characters safely, enabling shared query plans, and reducing SQL injection risk. section 7.4.1
- Treating malicious-looking input as a value rather than executable SQL code. section 7.4.1
Parameterized server lookup
Use Query = 'SELECT * FROM Servers WHERE Name = @name' with SqlParameter = @{name = 'Srv01'} instead of interpolating the server name into the query string. section 7.4.1
$DbaQuery = @{
SqlInstance = "$($env:COMPUTERNAME)\SQLEXPRESS"
Database = 'PoshAssetMgmt'
Query = 'SELECT * FROM Servers WHERE Name = @name'
SqlParameter = @{name = 'Srv01'}
}
Invoke-DbaQuery @DbaQuery
Shows the complete Invoke-DbaQuery argument hashtable with a named SQL placeholder and its separately supplied value. section 7.4.1
