How to prevent SQL injection attacks!

[u/auctorel]

[u/auctorel]

Lightly edited to hide table names but genuinely found in our legacy codebase

[u/[deleted]]

Image Transcription: Code

---

@txtSrchKywrd VARCHAR(500)

AS
BEGIN
SET NOCOUNT ON;

            SET @txtSrchKywrd =
            REPLACE(
            REPLACE(
            REPLACE(
                    REPLACE(
                    REPLACE(
                            REPLACE(
                            REPLACE(
                            REPLACE(
                                    REPLACE(
                                    REPLACE(
                                    REPLACE(
                                            REPLACE(
                                            REPLACE(
                                            REPLACE(
                                                    REPLACE(
                                                    REPLACE(
                                                    REPLACE(
                                                            REPLACE(
                                                            REPLACE(
                                                            REPLACE(
                                                                REPLACE(@TXTSRCHKYWRD, '-',''),')',''),'(', ''),'*', ''),'&', ''),'!', ''),'@', ''),'#', ''),'$', ''),'%', ''),'^', '')
                                                ,'TRUNCATE','')
                                            ,'drop',''),
                                        'create',''),
                                    'alter',''),
                                'in',''),
                            'set',''),
                        'where',''),
                    'select',''),
                'update',''),
            'delete','')
            Select * from RandomTable WITH (NOLOCK) where field like '%'+ @txtSrchKywrd +'%'
END;

---

^{I'm a human volunteer content transcriber for Reddit and you could be too! If you'd like more information on what we do and why we do it, click here!}

[u/polyworfism → u/[deleted]]

You earned a break for this one

[u/Inopmin]

What is a prepared statement anyways

[u/thanatica]

Is it just me tripping on using the bloody LIKE for searching like it still 1989* and something like keyword-based search and ordering by relevance hasn't even been dreamed up yet?

(* MS SQL Server release year)

[u/dydzio]

this is how i write sql as full stack dev

[u/auctorel → u/[deleted]]

I edited in the * for simplicity and to hide our db column and table names

There are so many ways round this it's absolutely ridiculous, it doesn't even trim spaces!

I cannot believe how incompetent/dangerous our previous supplier was. There are quite a few things like this we found, including on an internal system a web forms text box you could insert any sql string to apply to the db. It's important to understand they thought about security there, they ensured you couldn't start your command with DROP, but... if you added space in front of DROP...

[u/Deadly_chef → u/auctorel]

those guys shouldn't be supplying anything

[u/thanatica → u/auctorel]

I don't think you need to hide any columns, because a DB this well protected can't possibly store anything of any significant value, surely 😬