Escape Sequences....
-
Hello! I am developing a .NET 2.0 solution (C#) using ADO.NET I have a question regarding the escape sequences in SQL. Consider the following query
SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee's cost'Now when executed, this query generates an exception. Very well. The problem with with the single quote symbol. so what I do, I write like this:SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee\'s cost'But this still gives error... What the way out? Regards, MohsinPolite Programmer
More Object Oriented then C#
-
Hello! I am developing a .NET 2.0 solution (C#) using ADO.NET I have a question regarding the escape sequences in SQL. Consider the following query
SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee's cost'Now when executed, this query generates an exception. Very well. The problem with with the single quote symbol. so what I do, I write like this:SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee\'s cost'But this still gives error... What the way out? Regards, MohsinPolite Programmer
More Object Oriented then C#
replace with -SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee**''**s cost' the error occured because of 's so,u better replace ''s instead of 's
-
Hello! I am developing a .NET 2.0 solution (C#) using ADO.NET I have a question regarding the escape sequences in SQL. Consider the following query
SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee's cost'Now when executed, this query generates an exception. Very well. The problem with with the single quote symbol. so what I do, I write like this:SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee\'s cost'But this still gives error... What the way out? Regards, MohsinPolite Programmer
More Object Oriented then C#
when executing a query from c# application it is better to use parametrized query and there won't be any problems with escape characters.
-
Hello! I am developing a .NET 2.0 solution (C#) using ADO.NET I have a question regarding the escape sequences in SQL. Consider the following query
SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee's cost'Now when executed, this query generates an exception. Very well. The problem with with the single quote symbol. so what I do, I write like this:SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee\'s cost'But this still gives error... What the way out? Regards, MohsinPolite Programmer
More Object Oriented then C#
You should use parameterised queries instead of using escape sequences becuase the need to escape characters means that you are more likely to miss something and allow a SQL Injection Attack to take place. Please read SQL Injection Attacks and Some Tips on How to Prevent Them[^]
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
Hello! I am developing a .NET 2.0 solution (C#) using ADO.NET I have a question regarding the escape sequences in SQL. Consider the following query
SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee's cost'Now when executed, this query generates an exception. Very well. The problem with with the single quote symbol. so what I do, I write like this:SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee\'s cost'But this still gives error... What the way out? Regards, MohsinPolite Programmer
More Object Oriented then C#
Hi Mohsin, I don't think SQL syntax supports escape characters like that. To use an embedded single quote in a query like your example, you put two single quotes in the string: SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee''s cost'
Ron
-
Hi Mohsin, I don't think SQL syntax supports escape characters like that. To use an embedded single quote in a query like your example, you put two single quotes in the string: SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee''s cost'
Ron
That way SQL Injection Attacks lie.... Rather than escape characters, use parameterised queries.
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
That way SQL Injection Attacks lie.... Rather than escape characters, use parameterised queries.
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
Good advice, but it doesn't answer his question. *If* he is accepting user input to build the query, then SQL Injection attacks are an issue. If the entire query as written is part of his internal code, there is no danger.
Ron
-
Good advice, but it doesn't answer his question. *If* he is accepting user input to build the query, then SQL Injection attacks are an issue. If the entire query as written is part of his internal code, there is no danger.
Ron
Ron Savage wrote:
If the entire query as written is part of his internal code, there is no danger.
That's untrue. There is the possibility of a Second Order Attack. This is where supposedly clensed data that is already sitting in the database can be used to form an attack. All the data used is internal to the system at the time the SQL is formed, but the threat is just as real.
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
Ron Savage wrote:
If the entire query as written is part of his internal code, there is no danger.
That's untrue. There is the possibility of a Second Order Attack. This is where supposedly clensed data that is already sitting in the database can be used to form an attack. All the data used is internal to the system at the time the SQL is formed, but the threat is just as real.
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
Colin Angus Mackay wrote:
There is the possibility of a Second Order Attack. This is where supposedly clensed data that is already sitting in the database can be used to form an attack. All the data used is internal to the system at the time the SQL is formed, but the threat is just as real.
Another excellent bit of advice, yet again completely outside the context of the discussion. Neither the original question nor my response involved building a query from any external data. Without the inclusion of external text that may at some point have been entered by an end user of the program - SQL Injection attacks are not an issue.
Ron
-
Colin Angus Mackay wrote:
There is the possibility of a Second Order Attack. This is where supposedly clensed data that is already sitting in the database can be used to form an attack. All the data used is internal to the system at the time the SQL is formed, but the threat is just as real.
Another excellent bit of advice, yet again completely outside the context of the discussion. Neither the original question nor my response involved building a query from any external data. Without the inclusion of external text that may at some point have been entered by an end user of the program - SQL Injection attacks are not an issue.
Ron
Ron Savage wrote:
Another excellent bit of advice, yet again completely outside the context of the discussion.
Is it?!
Ron Savage wrote:
Neither the original question nor my response involved building a query from any external data.
Who's to say where the data came from? The OP certainly didn't. So any discussion on the matter is pure speculation without a definitive answer from the OP. As such, it becomes important that the issue of SQL Injection is raised... just in case.
Ron Savage wrote:
Without the inclusion of external text that may at some point have been entered by an end user of the program - SQL Injection attacks are not an issue.
Hardcoding fully formed SQL statements into one's program is hardly common. The more common scenario is that the statement contains some variable data. As such, in case the OP is giving a simplified example (which is common) it "yet again" becomes important to raise the issue of SQL Injection Attacks.
Upcoming events: * Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website