I meant restricting the single quote from the frontend user filter input. Simply we can ensure that the user can't input the single quote in the textbox.

@AkiT: If the restriction only exists in the frontend, then an attacker can simply bypass it. You need to address this in the backend by either using prepared statements (which is by far the most secure option) or the encoding-aware escape functions provided by the database library. Trying to prevent users from entering single quotes is nonsensical. And you still have the problem that you might screw up the character encoding.

@AkiT: As some of the commenters have already said, you should explain which database system and programming language you use. Then I'm pretty sure we can suggest a proper solution.

So in my case, if I escape the single quote successfully, it negates the SQL injection possibility by user text input right?

@AkiT: You would have to escape the entire input, not just individual characters. But the preferred method is to use prepared statements with, for example, the JdbcTemplate#query(String sql, PreparedStatementSetter pss, ...) methods. Yes, I understand it's slightly inconvenient to build the query template dynamically and pass the parameters in a loop. But security should be more important than developer convenience.

"your code and the database system may not agree on what a single quote actually looks like as a byte sequences." Single quote is an ASCII symbol, it is consistent across every character set and encoding.

@user13964273 The point is that other code points might, with a certain encoding, generate bytes that could be misinterpreted as a single quote. See the blog article linked from the answer.

@user13964273: There are a lot more character encodings than ASCII, and, no, they aren't all compatible with ASCII.

@AkiT A lot of attackers completely bypass your front end and send GET/POST/etc.. requests directly to your backend. It is easy to automate, and bypasses all of the pesky front-end restrictions.

@AkiT I mean this constructively, not unkindly: If you think that restricting input in the front end will result in safe back end code, then you are not experienced enough to say "we can't use prepared statements". You can, you just haven't learned how yet.

I'm fairly sure UTF8 is one of the things postgresql gets right, this should not be an issue,

@Jasen: There are many more character encodings available in PostgreSQL, including problematic ones like GBK. In an ideal world (from a security perspective), everybody would consistently use the same character encoding like UTF-8. But in reality, encoding mismatches do happen. In many cases, it only leads to harmless bugs (gibberish characters etc.). But there's no guarantee, and I don't think the OP should try it out.

@Jasen: In case my point wasn't clear: Yes, PostgreSQL by itself will absolutely handle encodings correctly. But once you start inventing your own filters outside of PostgreSQL (as the OP originally considered), then you're completely on your own.

UTF-8 is designed such that if you don't need to interpret the non-ASCIIi part the easiest way to process it is to ignore it. \x27 is always the single quote. and the single quote is always \x27.

@Jasen: Do you understand that there are more character encodings than UTF-8? Just look at the list I linked to. And, no, 0x27 isn't a single quote in every encoding.

Fair point, if they use MULE_INTERNAL for the database character set the client can't use UTF8 - I don't know if there are any quote masking exploits in that path.

The major takeaway: There is a reason, why SQL-injection is still in the top threads in 2024. It is easy to get wrong. Depending on your combination of programming language, database engine, driver and system configuration there can be gaps and history has shown writing your own function can easily fail.

This seems like a legacy multibyte encoding problem with the solution of always use a unicode encoding internally.

@Joshua: You’re missing the point. The GBK example is just the tip of the iceberg. There are other encodings which are also incompatible in terms of how they represent a single quote (like UTF-8 and UTF-16). And telling people to just use one encoding (like UTF-8) consistently is a bit like saying “just don’t make a mistake”. Developers do screw up encodings. The actual solution to the problem is to either use prepared statement (which doesnt depend on the right encoding )or the escape functions provided by the database library (which takes the encoding into account).

@Joshua: There are multiple Unicode encodings, and, no they aren't all compatible with each other in how they represent ASCII characters.

@Ja1024: If you know your input is SQL in some unicode encoding you can reliably determine the right one; or if your SQL receiver didn't implement that it can and should reliably throw on getting the wrong one due to unable to parse the initial statement keyword.

@Joshua: Consider the following case: The database connection and all queries are UTF-8-encoded. However, the home-made SQL escape/filter function incorrectly treats the user input as UTF-16 data. If an attacker provides the input 0x2027, this is perfectly valid in both UTF-8 and UTF-16. However, the same byte sequence is interpreted in two different ways: In UTF-8, it’s a space followed by a single quote (which can be used for injections). In UTF-16, it’s a harmless hyphenation point which has no special meaning in SQL.

@Ja1024: In which case a typecheck error would appear in both the language I use and the language OP uses due to trying to concatenate strings in different encodings. While the coverage is not complete (UTF-8 and legacy multibyte would concatenate without error), UTF-8 and UTF16 can't be concatenated without a type error. Good try though; PHP might have this problem.

@Joshua: You can concatenate UTF-8 and UTF-16 in Java. Just try it. I'm pretty sure that Java doesn't even retain any information about the encoding of a string, in which case the check you describe is impossible.

@Ja1024: No. Java's internal representation of a string is always in UTF-16; if you have direct UTF-8 pieces that haven't been changed to UTF-16 first they must be another type.

@Joshua: You're confused. There's just one string type in Java, and it's called String. You can create strings from raw byte sequences and an encoding argument. However, this encoding information isn't retained, so when Java concatenates two strings, it has no idea what the initial encodings were. It cannot throw an error (and it most definitely doesn't throw a type error). Just -- try -- it. There are languages like Ruby which keep the original encoding and will trigger an error for incompatible encodings, but that doesn't apply to Java (and an encoding error isn't a type error).

@Joshua: If you don't have a local JVM for testing, run this code online. If you get an exception complaining about incompatible encodings (or "types", as you call it), I'm thoroughly impressed.