Problem inserting ' in DB using PHP

[asivakrupa]

Hi,

I am creating a tool with PHP as front end and SQL Server as back end. I have some values to be inserted into the DB which contains the character ( ' ) i.e single quote.

I had errors when i tried to insert such values into the DB. So I used the replace() fn to replace a single single quote ( ' ) by two single quotes ( '' ). I was successful in it also. Values got inserted successfully.

But the problem now arises while trying to retrieve the values from the DB. A normal select query didn't work out. For example:

$query="select name from Table1";
$result=mssql_query($query);
$row=mssql_fetch_row($result);
$name=$row[0];
 

Consider the value of name here to be abc'def.

Please help me out with this.
Thanks in advance.

[Dormilich]

it is never a good idea to name DB fields with reserved characters…

try enclosing the name in backticks (`, `).

[asivakrupa]

Actually the field is a comments field where the user will give his input. It can also contain the single quote( ' ) which is the apostrophe character.

So you mean to say I should use back slash.. Ok...I will try out..

[asivakrupa]

hi,

Can you post any samples which use that back slashes for my reference?

Thanks in advance.

[Dormilich]

looks like the content was not properly escaped when inserting the value. go safe and use prepared statements.

in SQL special characters are escaped using the backslash.

[asivakrupa]

What do you mean by a back tick? Can you please explain me.?

Thanks in advance.

[Dormilich]

some mis-reading of mine, you can use backticks to "escape" the field names.

SELECT `field name` FROM table

see answer above.

[asivakrupa]

Hi,

I tried using " " for the fields. If you execute the following in SQL Query analyzer it works!!!

select "competitor_name" from Competitor
 

But in PHP you are going to include the above query as a string. So here the problem arises.

$query="select "competitor_name" from Competitor";
 

In the above code, the double quotes get mixed up resulting in errors.

Please help me out.
Thanks in advance.

[Dormilich]

as already stated field name may only be quoted by backticks (the double quotes terminate your sql string giving you an 'unexpected string' PHP error)

[Markus]

You need to escape single quotes, double quotes, new line characters, etc.

Check out what mysql_real_escape_string() does, and implement a version of your own for mssql.

[asivakrupa]

Hi,

I am not able to understand the backtick character.. Can you please explain me the same.?

Thanks in advance.

[Dormilich]

…or use Prepared Statements, where the SQL server does it for you.

[Markus]

@Dormilich
Just showing some different options, dormilich. Words like 'Prepared Statements' can seem intimidating to newbies.

[Dormilich]

@asivakrupa
backticks in the MySQL manual

[Dormilich]

I want to point out a (common) problem, you might sooner or later (hopefully never) be confronted with: SQL Injection (an attack technique to take over/exploit your database).

Once you're getting comfortable with using your database, I recommend reading more of that topic (some links from google) and how to work against it (again google). (…or just ask the experts at Bytes)

regards