Microsoft SQL Server: Using Placeholders

Summary

Placeholders are used to safely format values into an SQL query.

These values may come from the user running the task, an external system, or an API call.

Each placeholder value is automatically escaped to prevent SQL injection.

Using Placeholders

To put placeholders in a query use the ? character (you can supply as many as you need to).

Each value is supplied via the placeholders input.

Example

This example formats the first_name & last_name variables into a select query before running it.

Select Action

- mssql.client.select:
    query: >
      SELECT * FROM users WHERE first_name LIKE '%?%' and last_name LIKE '%?%'
    placeholders:
      - "{{ first_name }}"
      - "{{ last_name }}"
  load:
    mssql: mssql_secrets
  save: users

The SQL query run by PPA would look like:

Generated Query

SELECT * FROM users WHERE first_name LIKE '%John%' AND last_name LIKE '%Smith%'