Using Parameters in a Database Query From Excel Safely

A report that always asks for the same year is a snapshot, and the person who needs another year edits the SQL or asks you. A parameter solves this: a cell in the workbook holds the year, the query reads it, and a refresh returns the new figures. It is also a place where careless handling creates a security problem or a wrong report. This post shows how to set up parameters in Power Query, how to pass them to the database without pasting text into SQL, and how to keep the person using the workbook from getting a wrong answer. It is a spoke of connecting Excel directly to a database.

Two ways to hold a parameter

A Power Query parameter. In the editor choose Manage Parameters, New Parameter. Give it a name (StartDate), a type (Date), a current value and optionally a list of allowed values. Parameters appear in the Queries pane and can be used in any query. Changing the value means opening the editor and editing the parameter, which suits analysts.

A cell in the workbook. For people who never open Power Query, a named cell is friendlier. Put the value in a cell, make it a one-row table or a named range, and read it into Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="ReportParams"]}[Content],
    StartDate = Date.From(Source{0}[Start Date]),
    EndDate   = Date.From(Source{0}[End Date])
in
    [Start = StartDate, End = EndDate]

Name the table ReportParams, with columns Start Date and End Date and one data row. Convert the cell range to a Table (Ctrl+T) and name it in Table Design. The user edits the cells, presses Refresh All, and the query runs with the new values. Date.From converts the cell to a date and fails with an error if the cell holds text that is not a date, which is the validation you want.

The unsafe way and why it matters

The tempting approach is to build the SQL as a string:

Query = "SELECT * FROM orders WHERE region = '" & Region & "'"

If Region is North, this works. If someone types x' OR '1'='1 into the cell, the statement becomes WHERE region = 'x' OR '1'='1', which returns every row. With a destructive payload and a connection that is allowed to write, it could do worse. This is SQL injection, and it applies to a workbook cell as much as to a web form, because the cell is input from a person. A report workbook is normally used by trusted colleagues, which is not an excuse: workbooks get shared, cells get pasted into, and a read-only account limits the damage but not the leak.

The rule is the same everywhere: values travel as parameters, not as part of the statement text.

Bind the value with Value.NativeQuery

For connectors that support parameterized native queries, Value.NativeQuery takes a record of parameters and sends them separately from the SQL. With SQL Server:

let
    Source = Sql.Database("db01", "Shop"),
    Params = Excel.CurrentWorkbook(){[Name="ReportParams"]}[Content]{0},
    Result = Value.NativeQuery(
        Source,
        "SELECT o.OrderDate, c.Region, o.Total
         FROM dbo.Orders o JOIN dbo.Customers c ON c.Id = o.CustomerId
         WHERE o.OrderDate >= @start AND o.OrderDate < @end AND c.Region = @region",
        [start = Date.From(Params[Start Date]), end = Date.From(Params[End Date]), region = Text.From(Params[Region])],
        [EnableFolding = true]
    )
in
    Result

@start, @end and @region are placeholders. The record supplies their values with their types, and the server treats them as data. A region of x' OR '1'='1 is only a region with an odd name that matches nothing. The EnableFolding option lets Power Query still fold later steps into the statement where possible. Parameter support varies: SQL Server supports named parameters like this; some connectors accept it with different placeholder syntax or not at all. Check Microsoft's documentation for Value.NativeQuery and your connector before relying on it.

When the connector cannot bind parameters

With ODBC and some connectors, you may have no choice but to build the string. Reduce the risk:

• Convert to the proper type first. Dates through Date.ToText(d, "yyyy-MM-dd"), numbers through Number.ToText(n, "F2", "en-US"). A typed value cannot hold a quote.

• Validate text against a list. If the parameter is a region, restrict it to a dropdown fed by the allowed values, and check in the query that the value is in the list before using it:

Allowed = List.Buffer(Regions[Name]),
Region = if List.Contains(Allowed, RegionInput) then RegionInput else error "Unknown region",

• Escape quotes for any free text by doubling single quotes: Text.Replace(value, "'", "''"). That is a last resort; it handles the standard case and not every encoding trick.

• Use a read-only account so that even a successful injection can read only what that account may read (giving Excel users a read only database account).

A validated, typed value has few places to hide. Free text is the risky case.

Parameters in the navigator style

If you build the query with Power Query steps instead of SQL, a parameter works as a filter value: filter the OrderDate column to be on or after StartDate. Power Query folds the filter into the SQL as a bound or literal value, and the database filters before sending. This is safe and gives the server's index a chance. Check that folding happens by right-clicking the last step; View Native Query being enabled means the step folded. If it is greyed out, the step ran locally and downloaded more than it needed.

Make the parameter sensible for the person using it

A parameter is also an interface.

• Label it. A cell next to the table that says "Start date (inclusive)" saves a support call.

• Give defaults. A formula that computes the first of the previous month, =EOMONTH(TODAY(),-2)+1, keeps a monthly report current without edits. Power Query reads the cell's value on refresh.

• Validate the cell. Data Validation can restrict the cell to a date or to a list, so bad input is stopped at entry.

• Show what ran. Put the parameter values and the refresh time in the report header, for example ="Period: "&TEXT(Start,"yyyy-mm-dd")&" to "&TEXT(End,"yyyy-mm-dd"). A report that does not say which period it covers is a risk, because a forgotten value silently reports the wrong one.

• Order the pieces. Half-open ranges (>= start AND < end) avoid double-counting the boundary day and handle timestamps.

Time and time zones

Date parameters hide a trap. A datetime column compared with a date parameter treats the date as midnight, so an end date of 31 March excludes everything from 31 March after midnight. Use < end with the next day, as above. Time zones add another: if the database stores UTC and the report period is local, convert the boundaries, not the data. Document which one the parameters mean.

Refresh behavior

A parameter changes only when someone edits it. Refresh on open will run the query with whatever the cells hold. For scheduled refreshes, the values come from the workbook that is being refreshed (refreshing Power Query data on a schedule). If the cells use TODAY(), they update when the workbook calculates, which on open is usually before the refresh, though the order is not guaranteed in every setup; test it.

Checks

Run the report for a known period and compare with a direct query in the database tool. Try the boundary: the first and last day, a month with no data, a region with an apostrophe in the name (O'Brien). Try bad input: text in the date cell, an empty cell. Each should give a clear error or an empty result, not a wrong number. Save the test values beside the workbook so the next person can repeat them.

When a parameter in the workbook is the wrong control

The audience should not choose. If each region may see only its own, a parameter that anyone can change is not access control. Use a login that sees only that region.

The query is expensive. A parameter that lets people request ten years of detail invites a heavy refresh. Limit the range or provide pre-aggregated views.

The choices are many and interactive. A BI tool with filters and a published dataset serves exploration better than a cell and a refresh.

The values come from untrusted sources. Do not let outside text reach the database from a cell, even bound; treat it with the care of any user input.

Microsoft documents Value.NativeQuery and the Power Query page on using parameters. To convert a small result to JSON, use the CSV to JSON converter.