') closes the input for the original sql statement. So the actual input would be “Robert”, but it’s not really important for this kind of attack. ; says that the statement is over and anything after is a new statement. DROPTABLE customer; is that new statement, which deletes a table with the name “customers”. -- is the syntax for an sql comment. It effectively makes sure that any other sql statements in the actual script get ignored, so you don’t get a compile error.
This is an effective attack for when some programmer uses unsanitized string instertion in their sql script. In this case I could imagine a statement like: SELECT id FROM users WHERE name == {user_input}; where {user_input} is the literal, unsanitized input that you give on the website.
Notice that in this case, the ') doesn’t do anything, but it just becomes part of the input, so that is now “') Robert”.
As for the sanitisation, it can take many forms. Either characters that don’t usually appear in the context for that field (in terms of names, you can usually scrub most parentheses, more than one hyphen in a row etc) can be removed; copy it to a known encoded field such as unicode to get rid of characters with unusual properties; and making sure bounds are enforced to avoid overflows.
It should mean that your data is exactly that - raw data, and not commands or operands for the interpreter to act upon.
I don’t disagree, but throwing out the concept of prepared statements and parameterisation to someone who has asked for an explanation of the Bobby Tables jokes is a bit heavy going.
')closes the input for the original sql statement. So the actual input would be “Robert”, but it’s not really important for this kind of attack.;says that the statement is over and anything after is a new statement.DROP TABLE customer;is that new statement, which deletes a table with the name “customers”.--is the syntax for an sql comment. It effectively makes sure that any other sql statements in the actual script get ignored, so you don’t get a compile error.This is an effective attack for when some programmer uses unsanitized string instertion in their sql script. In this case I could imagine a statement like:
SELECT id FROM users WHERE name == {user_input};where{user_input}is the literal, unsanitized input that you give on the website.Notice that in this case, the
')doesn’t do anything, but it just becomes part of the input, so that is now “') Robert”.The obvious parade here is to be semi-illiterate when you create your database and name your field “costumer”
Spot on.
As for the sanitisation, it can take many forms. Either characters that don’t usually appear in the context for that field (in terms of names, you can usually scrub most parentheses, more than one hyphen in a row etc) can be removed; copy it to a known encoded field such as unicode to get rid of characters with unusual properties; and making sure bounds are enforced to avoid overflows.
It should mean that your data is exactly that - raw data, and not commands or operands for the interpreter to act upon.
Parameterisation entirely solves the problem without needing to sanitise the string
I don’t disagree, but throwing out the concept of prepared statements and parameterisation to someone who has asked for an explanation of the Bobby Tables jokes is a bit heavy going.
Not entirely (I recall seeing some obscure CVEs some years ago), but it’s a hell of a lot better than what some coders try to get away with.