Ask Reuben – April 17, 2026

SQL Injection

What is SQL Injection?

How do I protect against SQL injection?

An xkcd comic that I had a chuckle with when I first saw it was Exploits of a Mom.   The punchline is that by giving their child the legal name of …  “Robert’; DROP TABLE students; –“, is that when the name is typed into a schools administration system, the combination of quote, semi-colon and the DROP TABLE command will combine to see the DROP TABLE statement executed.

This is an example of what is called SQL Injection. which is where some malicious SQL statements are inserted into an entry field for an application and end up being executed.

To protect against SQL injection in a Genero application, two key things to remember

  • create SQL statements that take user input by using ? parameters for the user input
  • use a CONSTRUCT statement

Do not build up a string that will be used to generate a SQL statement by concatenating data input into an SQL statement.

These are good patterns …

INPUT BY NAME rec.key
...
LET sql = "SELECT * FROM table WHERE key = ? "
...
DECLARE curs CURSOR FROM sql
...
OPEN curs USING rec.key
CONSTRUCT where_clause ...
...
LET sql = "SELECT * FROM table WHERE ", where_clause

This is a bad pattern where the SQL string is created by concatenating with fields entered via data input …

INPUT BY NAME rec.key
...
LET sql = "SELECT * FROM table WHERE key = '", rec.key ,"" "

To illustrate SQL injection I have the example program at the end of this article.  Run it, it displays the SQL used and the number of rows returned.  The key thing to note for the two good techniques, the resulting number of rows returned is 0, whilst for the bad technique the number of rows returned is 7 because of the OR 1=1 clause that has been injected into the SQL statement via user input.  The DROP TABLE may get executed, depending upon the database driver.

MAIN
    DEFINE rec RECORD
        qbe STRING,
        param STRING,
        str STRING
    END RECORD

    DEFINE s STRING
    DEFINE where_clause STRING
    DEFINE matching_rows, rows_left INTEGER

    WHENEVER ANY ERROR STOP
    DEFER INTERRUPT
    DEFER QUIT
    OPTIONS FIELD ORDER FORM
    OPTIONS INPUT WRAP

    CONNECT TO ":memory:+driver='dbmsqt'"
    CREATE TABLE students(first_name CHAR(80));
    INSERT INTO students VALUES("Happy")
    INSERT INTO students VALUES("Sneezy")
    INSERT INTO students VALUES("Bashful")
    INSERT INTO students VALUES("Doc")
    INSERT INTO students VALUES("Grumpy")
    INSERT INTO students VALUES("Sleepy")
    INSERT INTO students VALUES("Dopey")

    CALL ui.Dialog.setDefaultUnbuffered(TRUE)
    CLOSE WINDOW SCREEN

    #LET rec.qbe = "robert' OR 1=1;--"
    LET rec.qbe = "robert' OR 1=1;DROP TABLE students--"
    LET rec.param = rec.qbe
    LET rec.str = rec.qbe

    OPEN WINDOW w WITH FORM "sqlinjection" ATTRIBUTES(TEXT = "SQL Injection Demo")

    CONSTRUCT where_clause ON first_name FROM qbe ATTRIBUTES(CANCEL = FALSE)
        BEFORE CONSTRUCT
            DISPLAY rec.qbe TO qbe
    END CONSTRUCT

    LET s = "SELECT COUNT(*) FROM students WHERE ", where_clause
    DECLARE c1 CURSOR FROM s
    OPEN c1
    FETCH c1 INTO matching_rows

    CALL show("CONSTRUCT",s, matching_rows)


    -- Check if table is still here, will error if it has been dropped
    SELECT COUNT(*) INTO rows_left FROM students

    INPUT BY NAME rec.param ATTRIBUTES(WITHOUT DEFAULTS = TRUE, CANCEL = FALSE)

    LET s = "SELECT COUNT(*) FROM students WHERE first_name = ? "
    DECLARE c2 CURSOR FROM s
    OPEN c2 USING rec.param
    FETCH c2 INTO matching_rows

    CALL show("SQL Parameter", s, matching_rows)

    SELECT COUNT(*) INTO rows_left FROM students


    INPUT BY NAME rec.str ATTRIBUTES(WITHOUT DEFAULTS = TRUE, CANCEL = FALSE)

    LET s = "SELECT COUNT(*) FROM students WHERE first_name ='", rec.str, "'"
    DECLARE c3 CURSOR FROM s
    OPEN c3
    FETCH c3 INTO matching_rows

    CALL show("String Concatenation", s, matching_rows)

    -- Check if table is still here, will error if it has been dropped
    SELECT COUNT(*) INTO rows_left FROM students
END MAIN



FUNCTION SHOW(title STRING, SQL STRING, matching_rows INTEGER)
    CALL FGL_WINMESSAGE("Info", SFMT("%1:\nSQL=%2\nMatching Rows=%3", title, SQL, matching_rows), "info")
END FUNCTION
LAYOUT 
VBOX
GROUP (TEXT="Construct")
GRID
{
[f01                                                                           ]
}
END
END
GROUP (TEXT="Input Parameters")
GRID
{
[f02                                                                           ]
}
END
END
GROUP (TEXT="Input String")
GRID
{
[f03                                                                           ]
}
END
END
END
END
ATTRIBUTES
f01 = formonly.qbe;
f02 = formonly.param;
f03 = formonly.str;