I assume if I use prepared statements and then escape all ' characters, no input any user dished up could cause me any trouble. But I know there are lots of tricky hacks out there and my assumptions of my database's impenetrableness are often fallible. This question assumes I as the developer am not using any fancy riff-raff, like PostgreSQL string constants with C-style escapes, for example.
As a sample of my 'foolproof' input sanitization, I have the following script (this question is language-agnostic for the scripting code, but for reference, the snippet is in Python using the psycopg2 library):
user_input = function_that_returns_user_input_string()
#replace any single quotes with two single quotes
modified_input = user_input.replace("'", "''")
cursor.execute(
"PREPARE mystatement AS "
"SELECT * FROM myschema.mytable WHERE mycolumn=$1;"
)
cursor.execute(
f"EXECUTE mystatement ('{modified_input}');"
)
res = cursor.fetchall()
function_that_handles_data_and_does_etc()
Is there anything function_that_returns_user_input_string() could return to do SQL injection or any other hack? And if so, what?
In addition, let's go with a scenario where my script has a simple INSERT statement instead of a SELECT one, if that makes any difference in the answer to my question.
