How to edit SQL statement with user input

[domweng]

Hello,

I am currently needing to know for some work I am trying to do whether it is available to take some form of user input and use that in the SQL statement in my code.

For example, when I'm using something such as...

sql = "SELECT team_name FROM TEAM WHERE team_div = 'North West'"

Is there a way that I could possibly ask the user, "what division would you like to see the teams in?" And if the user for example, enters "North East," the sql would then be changed to...

sql = "SELECT team_name FROM TEAM WHERE team_div = 'North East'" 

(or another division they want to see the teams in)

[jonnin]

yes, you can do that.
you just set up a list for them to pick from (if UI driven, a drop down box of choices, if text based, a list they can view and pick by number) and they pick it, you swap in the text you want.
there are different ways to do it, you can put a token in the string and replace it, or break the string up and put the pieces back together with + ops, etc.

[kigar64551]

Just be sure to never ever insert a user-provided input (string) directly into an SQL statement! 😱

This is because an SQL injection vulnerability would be the result:
https://en.wikipedia.org/wiki/SQL_injection

Instead, use a prepared statement whenever you need to parameterize an SQL statement:
https://en.wikipedia.org/wiki/Prepared_statement

Example with SQLite:

void example(sqlite3 *const db, const char* input_text)
{
    // SQL statement ('?' is a placeholder)
    const char *sql = "SELECT team_name FROM TEAM WHERE team_div = ?";

    // Create prepared statement
    sqlite3_stmt *stmt;
    sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

    // Bind placeholder to user input
    sqlite3_bind_text(stmt, 1, input_text); // <-- e.g. input_text = "North West"

    // Execute!
    sqlite3_step(stmt);

    // Clean up
    sqlite3_finalize(stmt);
}

(One could argue that if the string comes from a "drop down box" that is under you control, and there is no way for the user to enter a custom string, then it would be safe to use the given string directly in the SQL statement. But better safe than sorry! So, if a string is supplied by the user in any way, then that string should always be treated as an "untrusted" string. Therefore a prepared statement should always be used!)

[jonnin]

and, if you are worried about untrusted users, use an account (your program's credentials) with the least possible access to get the job done: if they just need to read, then it gets read only access, no update/write/create etc. Then their damage is limited if they manage to do something stupid by design or accident.

it is not always hard to reverse engineer a program to hack the prepared statements -- the coder has to jump through a lot of hoops to prevent this... But if the program can't do anything destructive with its access level, the worst they can do is read extra info.

[mbozzi]

https://xkcd.com/327/