Page 1 of 1

how to replace ' character using preg_replace

Posted: Tue Oct 25, 2005 9:57 am
by fy04
Hi there,

I need to replace single quote character to two single quotes then I can update the table in SQL server. For example, change the name "O'DONNELL" to "O''DONNELL". I tried to use preg_replace to do it but it seems the ' charater is so special that I cannot do it. Any help is appreciated.

Thanks in advance.

Yolande

Posted: Tue Oct 25, 2005 10:14 am
by Jenk
There is no need to replace, if you are escaping correctly :)

When you say SQL Server, do you mean MS SQL Server?

Anywho, if you really needed to change all single quotes to doubles:

Code: Select all

$string = str_replace("'", '"', $string);

Posted: Tue Oct 25, 2005 10:42 am
by fy04
Hi,

I tried the code $string = preg_replace("/\\'/", "''", $string);and it worked.

The SQL server is MS SQL server. What do you mean "There is no need to replace, if you are escaping correctly"? :oops:
Please give me more hints.

Thanks again.

Yolande

Posted: Tue Oct 25, 2005 11:24 am
by Maugrim_The_Reaper
The SQL language uses quotes (single) to encapsulate string values. So what you need to do is escape the single quote:

O\'DONNELL

This \ tells SQL (except in MSSQL) to ignore the quote following the slash. So this is storable to the database and should produce no errors. Escaping also escapes other special characters - esp. ones that can be used to create an SQL Injection. You should filter all input and escape all output.

MSSQL (back to your question) uses a double quote to escape single quotes.

Your best bet is a REGEX free attempt using:

Code: Select all

$data = str_replace("'", "''", $data);

Posted: Tue Oct 25, 2005 11:57 am
by fy04
Thank you so much. This is really helpful.