An SQL variable is a named placeholder in a query whose value is supplied separately. Declare it with @, then pass a hashtable through Invoke-DbaQuery’s -SqlParameter argument. section 7.4.1
Parameterizing values makes dynamic SQL safer and more predictable:
- Checks that supplied values use appropriate data types. section 7.4.1
- Escapes characters and converts values such as DateTime into SQL-compatible formats. section 7.4.1
- Allows SQL to reuse a query plan for different values and helps prevent SQL injection. section 7.4.1
Parameterized server lookup
Use Query = 'SELECT * FROM Servers WHERE Name = @name' with SqlParameter = @{name = 'Srv01'} in the Invoke-DbaQuery argument hashtable. Unlike string interpolation, a malicious server name remains a string value rather than executable SQL. section 7.4.1
$DbaQuery = @{
SqlInstance = "$($env:COMPUTERNAME)\SQLEXPRESS"
Database = 'PoshAssetMgmt'
Query = 'SELECT * FROM Servers WHERE Name = @name'
SqlParameter = @{name = 'Srv01'}
}
Invoke-DbaQuery @DbaQuery
Inspect the complete Invoke-DbaQuery example, including the SQL variable, connection settings, database, and parameter hashtable. section 7.4.1
