Parameterized Queries

billwinker

Question for the developers.

Best practice is the use of parameterized queries for Sql.  When running queries using powershell and the api, I have not found a way to utilize parameterized queries since we interface with swql, not directly with the sql interface.  It doesn't appear that we have the objects available to query using parameterized queries.  Does the swql interface have the controls in place to guard against sql injection?  If not, is there a way to write the query in a parameterized manner?

tdanner

SWQL is safe against SQL injection. This was designed in and has been carefully tested to verify.

However, using query parameters in your SWQL queries is also a good idea. For one thing, it helps with reusing SWQL query plans which is good for response time and SWIS memory usage.

To use a parameterized query in PowerShell, use this syntax: (documented here)

Get-SwisData $swis "SELECT DisplayName, Status FROM Orion.Nodes WHERE Vendor = @vendor" -Parameters @{ vendor = "Cisco" }

To use parameterized queries via https/json, make a POST request to https://server:17778/SolarWinds/InformationService/v3/Json/Query with a body like this: (documented here)

{

  "query": "SELECT DisplayName, Status FROM Orion.Nodes WHERE Vendor = @vendor",

  "parameters": {

   "vendor": "Cisco"

  }

}