Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replacing symbols in SQLQuery

Status
Not open for further replies.
Forecaster71

Forecaster71

Technical User
Apr 12, 2008
7
0
0
SE


If a string that is going from a text area in a database contains ' the SQLquery is interrupted.
To avoid this I have to replace this ' with another symbol that does not affect the SQLquery.
How do I accomplish this?

 
Sort by date Sort by votes
Substitute each ' with ''. I know this works for Oracle.

so "it's 'time'" becomes "it''s ''time''
 
Upvote 0 Downvote
I could do that... but I want it to do it automatically so my users don't have to think about it.
 
Upvote 0 Downvote
Then change the code that handles the DML to replace each ' with ''. Its hard to give more details without knowing your specific implementation. But say for example you're using PHP

Code: 
$query = str_replace("'","''",$input);
 
Upvote 0 Downvote
I'm not familiar with ASP. But the concept is the same. Take the inputs, replace every ' with '', then perform the sql.
 
Upvote 0 Downvote
Somewhere in save_user.asp, you must be building a SQL query that you pass to Access, right?

In asp, you would use the replace function in a similar way that jaxtell shows. The syntax is slightly different in ASP, but the concept is the same.

Code: 
SQL = Replace(SQL, "'", "''")

Better yet... Learn how to use a command object with parameters.

If you think it's not important to do this, I encourage you to read up on [google]SQL Injection[/google].

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
In JScript:
Code: 
var newSQL, re;
re = /'/g;
newSQL = oldSQL.replace(re, "''");

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
windows command in a sql script
Replies
1
Views
120
carp
carp
BobThornton
  • Locked
  • Question
Cross Join in an Insert Into
Replies
1
Views
121
carp
carp
harfri
  • Locked
  • Question
Insert into a new table
Replies
0
Views
76
harfri
harfri
NickyJay
  • Locked
  • Question
SQL 2008 exclude parent records in results where multiple child records exist in 2nd table
Replies
4
Views
114
PHV
PHV
dEX2340
  • Locked
  • Question
Using CONVERT in CASE WHEN
Replies
4
Views
101
dEX2340
dEX2340

Part and Inventory Search

Sponsor

Back
Top