How to handle hyphens in SQL insert

[Karma]

Hi,

I'm reading data from a flat file and inserting each record into a DB2 database.

Multiple lines in the input data have quotes (O'NEIL for example), so the following code fails on those records:

$addRecord = "INSERT INTO ".$DB2file." values('" . mysql_real_escape_string($currentLine) . "')";
$result = odbc_exec($connect, $addRecord);

Each record I'm adding to the DB2 database must be an exact replica of the data I'm reading, so I need to keep these quotes in the DB2 database, I can't simply replace/escape them as I normally do.

Help! :)

[Matthew1980]

Hi there Karmm,

Have you tried htmlentities() at all? That may do the job as you are asking it too.

The structure should be like this too:-

INSERT INTO `tablename` (`col1`,`col2`) VALUES ('value1', 'value2');

So the structure you have probably wouldn't work, as your not stipulating a field name to put the data.

Hope I have understood the issue correctly,

Cheers,
MRb

[Karma]

Hi Matthew1980, htmlentities will simply convert the quote to its HTML code.

I want keep the actual quotes in the written record.

I'm sure there's a really simple way of doing this :/

(Also, you don't NEED to specify the column names and in this case its easier not to)

[jatar_k]

but you are using mysql_real_escape_string, which is for mysql

it escapes ' as \' but in db2 I believe you need to escape a single quote by preceding it with a single quote, not a slash

[Karma]

I'm actually inserting these records via Microsoft's ODBC.

<?php

include_once("config.php");

$currentLine = "O'NEIL";

$addRecord = "INSERT INTO $DB2file values('$currentLine')";
$result = odbc_exec($connect, $addRecord);

?>

Gives me the following:

---------------------------------------------------------
Warning: odbc_exec() [function.odbc-exec]: SQL error: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token 'O' was not valid. Valid tokens: DAY PATH YEAR LABEL MONTH OPTION RESULT CONNECTION TRANSACTION., SQL state 37000 in SQLExecDirect in C:\localhost\example.php on line 8
---------------------------------------------------------

Still stuck and still reading!

[Alcoholico]

You are using mysql_real_escape_string() which requires an open MySQL connection in order to work, otherwise it'll do nothing.
You should try addslashes() instead of mysql_real_escape_string()
See: [uk3.php.net...]
Hope that helps.

[Karma]

I've removed the mysql_real_escape_string() now (see above).

addslashes() will add slashes to the written record, which isn't what I need.

[jatar_k]

you need to check the string for ' and replace it with ''

[Karma]

Yep, just tried that...

Warning: odbc_exec() [function.odbc-exec]: SQL error: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token 'O''NEIL' was not valid. Valid tokens: DAY PATH YEAR LABEL MONTH OPTION RESULT CONNECTION., SQL state 37000 in SQLExecDirect in C:\localhost\example.php on line 14

[Matthew1980]

Hi there Karma,

Sounds like the string itself is causing an error - have you tried it as just ONEIL to see if it accepts the "whole" string? as you may have to do a str_replace to remove the ' and attach something else, then replace when retrieving the data, I know its convoluted but, if it works...

[EDIT]: Sorry, just re-read your first post, str_replace wouldn't do any good - ignore that..

Referring to the sql structure, it just seemed to be missing the parameter of a colum name, I know that you don't need to specify them if you are not inserting to them all, on first read the syntax seemed incomplete, that's all :)

Cheers,
MRb

[jatar_k]

>> Token 'O' was not valid
>> Token 'O''NEIL' was not valid

given that those 2 are different I think the escaping is now correct, I also think you might have a quoting issue

$addRecord = "INSERT INTO ".$DB2file." values('" . mysql_real_escape_string($currentLine) . "')";

you have quotes around the outside of $currentline, does the contents of that var take that into account?

<added>all the searches I have done refer to invalid syntax resulting in that particular error

[Matthew1980]

Jatar_k:

'O''NEIL'

Would explain that...

Good luck anyway..

Cheers,
MRb

[rocknbil]

This is an issue I deal with in Perl in a different way. PHP, when using mysql_real_escape_string, turns

O'Neal

into

O\'Neal

Correct, this is the problem, not hyphens?

Normally, on a Linux mySQL server, per the recommendations of the mySQL documentation, you would indeed double up the quote, which does work

O''Neal

even in PHP, without mysql_real_escape_string, but obviously your O.S./database server is not allowing it. I'd look into the documentation of the database server, this is not a PHP problem.

Some ideas that might work:

1. Change your quoting.

insert into table (field) values ("O'Neal");

This will now present problems for double quotes inside the content, but if this is to be for HTML page display, you want quotes htmlencoded anyway.

For searching issues, all you need to do really is make exceptions when searching:

double quoted -> $_POST['term'] = 'I want "a widget" for Christmas';

$_POST['term'] = str_replace('"','&quot;',$_POST['term']);

//or $_POST['term'] = preg_replace_replace('/"/','&quot;',$_POST['term']);

2. Replace troublesome characters with ASCII equivalents:

$_POST['val'] = "O'Neal";

$_POST['val'] = str_replace("'",'&#047;',$_POST['term']);

Then apply the same search exception above (might have the wrong code there, htmlentities will probably do the same thing, but you get the idea . . . )