SQL language injection help

Hi there,

On one hand: the Language Injection rule would be the best as it will work in all places. On another hand – it can cause false positives (if you have a plain string that starts with a similar sequence).

Can I declare a function parameter with a lang attribute so it's always interpreted as SQL at the call site?

Yes, if it's a code that you can edit. Use special #[Language] PHP Attribute by JetBrains:

<?php
function find(#[Language("SQL")] string $sql) {…}

If it starts with “WITH”

Do you have a few examples of such a strings? I have this rules, seems to work (a clone from similar rule for JS context):

Both approaches work great. Thank you so much!

And since you asked for an example:

WITH expanded_multipacks AS (
    SELECT id, mp_model, pack, pack_num
    FROM (SELECT id,
             model AS mp_model,
             packs,
             UNNEST(ARRAY [pack1_ref2, pack2_ref2, pack3_ref2, pack4_ref2, pack5_ref2]) AS pack,
             generate_series(1, 5) AS pack_num
         FROM tblups_multipacks) _inner
    WHERE pack_num <= packs -- ignore unused packs
)
SELECT coalesce(pack, modelnumber) AS model,
       coalesce(mp_model, '') AS mp_model,
       qty,
       shipqty
    FROM fun_display_shipment_status($1)
    LEFT OUTER JOIN expanded_multipacks ON mp_model = modelnumber
WHERE shipqty > 0;
 

Thanks, the aforementioned injection rule works for that code just fine, even for a variable (e.g. $sql = “WITH…”).

P.S. There is another way of injecting the same (can work with any function, even if it's not your code / the code that you cannot edit). But there are some restrictions:

1. It matches by a function name. So the name must pretty unique.
2. Only works for the first parameter.

Could be used for 3rd party code (if the above conditions are OK).

Thanks for the followup. That's good to know!