Dealing with special characters in SQL queries
-
Alright, I'm about ready to yank what's left of my hair out. Hopefully someone out there has the answer to my problem. Here it is… I want to allow users to type any damn fool thing they want into a text field and then store that string in a database. I don’t like the idea of disallowing certain characters. I’d like to figure a way to store any string in the database regardless of what silly characters the user inputs. I’ve done quite a bit of looking around on the internet for the answer to this problem. Surprisingly no one seems to have a good answer. I thought this would be easy and that I would find a viable answer right away. I have to admit that I am a little disappointed with the development community as a whole right now. For shame! Anyway, I figured out how to deal with apostrophes and “single-qoutes”. ‘ this character whatever you call it anyway. If you simply double-up an apostrophe in a SQL query, for example: the string “Mr. O’Connell” would be changed to “Mr. O’’Connell”. This seems to work fine. Here’s the problem that’s killing me, what if the string was “Mr. O”Connell”, now what do you do. Obviously, it would be stupid to use a quotation mark it this way, but I want to allow users to indulge their stupidity. How in the heck do you put that in a SQL query without invoking a database error? Also, what about other special characters like: \ @ # $ % and so on. What if the string was something like this “Mr. “\@#$’%”. There has got to be an answer. Not that it matters a whole lot, but the application is written in VB.Net and I am querying against a MS Access database. Access is just for now, when the application is finished users will have the option of several different providers such as: Access, SQL Server or My SQL. Please Help
-
Alright, I'm about ready to yank what's left of my hair out. Hopefully someone out there has the answer to my problem. Here it is… I want to allow users to type any damn fool thing they want into a text field and then store that string in a database. I don’t like the idea of disallowing certain characters. I’d like to figure a way to store any string in the database regardless of what silly characters the user inputs. I’ve done quite a bit of looking around on the internet for the answer to this problem. Surprisingly no one seems to have a good answer. I thought this would be easy and that I would find a viable answer right away. I have to admit that I am a little disappointed with the development community as a whole right now. For shame! Anyway, I figured out how to deal with apostrophes and “single-qoutes”. ‘ this character whatever you call it anyway. If you simply double-up an apostrophe in a SQL query, for example: the string “Mr. O’Connell” would be changed to “Mr. O’’Connell”. This seems to work fine. Here’s the problem that’s killing me, what if the string was “Mr. O”Connell”, now what do you do. Obviously, it would be stupid to use a quotation mark it this way, but I want to allow users to indulge their stupidity. How in the heck do you put that in a SQL query without invoking a database error? Also, what about other special characters like: \ @ # $ % and so on. What if the string was something like this “Mr. “\@#$’%”. There has got to be an answer. Not that it matters a whole lot, but the application is written in VB.Net and I am querying against a MS Access database. Access is just for now, when the application is finished users will have the option of several different providers such as: Access, SQL Server or My SQL. Please Help
If you use parameters in your query you should get around this problem. No?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
-
Alright, I'm about ready to yank what's left of my hair out. Hopefully someone out there has the answer to my problem. Here it is… I want to allow users to type any damn fool thing they want into a text field and then store that string in a database. I don’t like the idea of disallowing certain characters. I’d like to figure a way to store any string in the database regardless of what silly characters the user inputs. I’ve done quite a bit of looking around on the internet for the answer to this problem. Surprisingly no one seems to have a good answer. I thought this would be easy and that I would find a viable answer right away. I have to admit that I am a little disappointed with the development community as a whole right now. For shame! Anyway, I figured out how to deal with apostrophes and “single-qoutes”. ‘ this character whatever you call it anyway. If you simply double-up an apostrophe in a SQL query, for example: the string “Mr. O’Connell” would be changed to “Mr. O’’Connell”. This seems to work fine. Here’s the problem that’s killing me, what if the string was “Mr. O”Connell”, now what do you do. Obviously, it would be stupid to use a quotation mark it this way, but I want to allow users to indulge their stupidity. How in the heck do you put that in a SQL query without invoking a database error? Also, what about other special characters like: \ @ # $ % and so on. What if the string was something like this “Mr. “\@#$’%”. There has got to be an answer. Not that it matters a whole lot, but the application is written in VB.Net and I am querying against a MS Access database. Access is just for now, when the application is finished users will have the option of several different providers such as: Access, SQL Server or My SQL. Please Help
-
If you use parameters in your query you should get around this problem. No?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
Could you give me an example?
-
Well, stop writing SQL Insert / Update Queries and start using Objects. ADO (and now ADO.NET) lets you enter any damn thing you want into the DB! It has for many years! ;P
Yea, But I hate datasets. They just seem so redundant to me. I already have a database so what's the point of making a copy of the tables in code. I already have tables, there in the database. Plus, I've already got everything working, so to change everything over to use those silly datasets would take a lot of time that I don't have. Sending Non-Queries and using data readers is faster anyway.
-
Well, stop writing SQL Insert / Update Queries and start using Objects. ADO (and now ADO.NET) lets you enter any damn thing you want into the DB! It has for many years! ;P
And why are you stiking your tounge out at me. That's not nice. :((
-
Could you give me an example?
Here is an example on MSDN: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbcommandclassparameterstopic.asp[^]
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
-
And why are you stiking your tounge out at me. That's not nice. :((
I used to think exactly like you. The point is not redundancy though, it's ease of programability (I think I just made that word up) and also being able to handle unforseen problems before they even occur (i.e. handling any character). Did you know that you can bind DataAdapters to Stored Procs and also bind the Parameters of the Stored Procs to Columns in DataSets? But, whatever floats your boat. You might want to Build seperate routines then for Save and Delete operations that use DataCommand Objects.
-
I used to think exactly like you. The point is not redundancy though, it's ease of programability (I think I just made that word up) and also being able to handle unforseen problems before they even occur (i.e. handling any character). Did you know that you can bind DataAdapters to Stored Procs and also bind the Parameters of the Stored Procs to Columns in DataSets? But, whatever floats your boat. You might want to Build seperate routines then for Save and Delete operations that use DataCommand Objects.
mikasa wrote: Did you know that you can bind DataAdapters to Stored Procs and also bind the Parameters of the Stored Procs to Columns in DataSets? You know, I just discovered that earlier today. I'm almost sold of the idea that datasets are acceptable. Once the typed datasets are sorted out, and apparently there are vast improvements in this area in .NET 2.0, I might actually "like" them.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
-
mikasa wrote: Did you know that you can bind DataAdapters to Stored Procs and also bind the Parameters of the Stored Procs to Columns in DataSets? You know, I just discovered that earlier today. I'm almost sold of the idea that datasets are acceptable. Once the typed datasets are sorted out, and apparently there are vast improvements in this area in .NET 2.0, I might actually "like" them.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
I'm with you there. In VB6, I never used databinding! I hated it and it was flaky. Now, however, .NET has made vast improvements with DataBinding! I've been able to Code Business Layers and UIs with very little code at all! Not only that, it's great having the ability to Map DataColumns to DataCommand Parameters and be able to use Stored Procs to update data. And it doesn't stop there, you can even get Values from the DB with "Output" Parameters! :-D
-
Alright, I'm about ready to yank what's left of my hair out. Hopefully someone out there has the answer to my problem. Here it is… I want to allow users to type any damn fool thing they want into a text field and then store that string in a database. I don’t like the idea of disallowing certain characters. I’d like to figure a way to store any string in the database regardless of what silly characters the user inputs. I’ve done quite a bit of looking around on the internet for the answer to this problem. Surprisingly no one seems to have a good answer. I thought this would be easy and that I would find a viable answer right away. I have to admit that I am a little disappointed with the development community as a whole right now. For shame! Anyway, I figured out how to deal with apostrophes and “single-qoutes”. ‘ this character whatever you call it anyway. If you simply double-up an apostrophe in a SQL query, for example: the string “Mr. O’Connell” would be changed to “Mr. O’’Connell”. This seems to work fine. Here’s the problem that’s killing me, what if the string was “Mr. O”Connell”, now what do you do. Obviously, it would be stupid to use a quotation mark it this way, but I want to allow users to indulge their stupidity. How in the heck do you put that in a SQL query without invoking a database error? Also, what about other special characters like: \ @ # $ % and so on. What if the string was something like this “Mr. “\@#$’%”. There has got to be an answer. Not that it matters a whole lot, but the application is written in VB.Net and I am querying against a MS Access database. Access is just for now, when the application is finished users will have the option of several different providers such as: Access, SQL Server or My SQL. Please Help
Use of special characters in datatable.select(sql): Encapsulate each of these special chars with "[" and "]". e.g [ will become [[] ] will become []] % will become [%] so "text [abc] test" should look like "text [[]abc[]] test" The simple use of string.replace will not help:
.Replace("[","[[]").Replace("]","[]]")
will produce: "text [[[]]abc[]] test" (not really what we expected). Esp. for brackets you will have to go through some special "escapes":
.Replace("[","<ESCAPE FOR OPEN>").Replace("]","<ESCAPE FOR CLOSE>").Replace("<ESCAPE FOR OPEN>","[[]").Replace("<ESCAPE FOR CLOSE>","[]]")
worked fine for me.