Can SQL server escape single quotes while producing a sql injection on sql server? And how do SQL server escape single quote?
I found a simple breakout in a where clause in one of our projects where the code is so old that they claim they can't use parameters to communicate with ms SQL server. It's written in C++, I can't read this language and I'm not able to access the code in any way. To illustrate the issue I added an example of a non harmful statement, the bold part is what the user can directly input SELECT '1 ' SELECT 2--';
The single quote after 1 is causing the breakout in this scenario. This is what I've shown to the team. They then did a detection in code that searches for single quotes and add three other single quotes after each single quote they find. Thus the example would become this SELECT '1'''' SELECT 2--'; I don't like this solution at all, but I can't find a way to breakout any more. The fields inside the database are stored treated as nvarchar characters. Is there still a possibility to bypass this practice in any kind? How do SQL server escape single quote?
The simplest method to escape single quotes in Oracle SQL is to use two single quotes. For example, if you wanted to show the value O'Reilly, you would use two quotes in the middle instead of one. The single quote is the escape character in Oracle SQL. If you want to use more than one in a string, you can. No escaping is used with single quotes. Use a double backslash as the escape character for backslash. the code is so old that they claim they can't use parameters to communicate with ms SQL server That's a lousy excuse. It's like saying "your car is so old, we can't build in seatbelts". They might have to redo a lot of work with more modern techniques, but it can be done. The fix they applied is escaping, which is a correct solution (second best to parameterised queries). To escape an apostrophe (or 'single quote') in MSSQL, you can prefix it with another apostrophe. Why did the developers add two more? The only explanation I can think of, is that the output is interpreted as SQL again, but honestly I don't know and it should create bugs if nothing else. As for how to break out of it, that part of your question seems to be a duplicate of this one (though yours is specific for MSSQL):How to defeat doubling up apostrophes to create SQLi attack?The answer is basically: [...] you may be able to force various SQL databases to translate unicode to the local charset. For example, Ā could be converted to A. Even worse: U+02BC, or ʼ would be translated as ', which is U+0027. This is calledUnicode-based Smuggling.