An injection attack occurs when untrusted input is interpreted as executable code or commands. In SQL automation, dynamically joining input into a query can let an attacker alter the statement, including destructive commands. section 7.4.1
Prevent SQL injection by parameterizing values:
- Write a query with placeholders such as @name instead of interpolating variables into SQL strings. section 7.4.1
- Pass parameter values through the Invoke-DbaQuery -SqlParameter argument and a hashtable. section 7.4.1
- Build dynamic WHERE clauses from permitted parameters while keeping their values separate from the SQL text. section 7.4.1
Unsafe versus parameterized filtering
Interpolating a server name can turn the value "';Truncate table xyz;select '" into executable SQL. With `WHERE Name = @name` and `SqlParameter = @{name = 'Srv01'}`, SQL treats the same malicious input as a 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
Inspect a parameterized Invoke-DbaQuery call using a named SQL placeholder and hashtable value. section 7.4.1
