Replacing symbols in SQLQuery

[Forecaster71]

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?

 

[jaxtell]

Substitute each ' with ''. I know this works for Oracle.

so "it's 'time'" becomes "it''s ''time''

 

[Forecaster71]

I could do that... but I want it to do it automatically so my users don't have to think about it.

 

[jaxtell]

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

$query = str_replace("'","''",$input);

 

[Forecaster71]

You can check the page in question here:

http://darkestdarkness.somee.com/index.htm

I'm using ASP pages with Javascript and SQL

 

[jaxtell]

I'm not familiar with ASP. But the concept is the same. Take the inputs, replace every ' with '', then perform the sql.

 

[gmmastros]

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.

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

 

[PHV]

In JScript:

var newSQL, re;
re = /'/g;
newSQL = oldSQL.replace(re, "''");

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886