Apostrophe - when used it mucks with my odbc

[Johnny BeGood]

Hi All,

When a user enters an Apostrophe into a text area field on a form, i.e.
didn't, it mucks with odbc as follows

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression ''didn't',

Whats the best way to handle this, other than not entering the apostrophe.
I'm stuck with the current backend database as its part of an existing
application.

Any help gratefully appreciated, even with humour I may not understand!

Cheers

[Michael Fesser]

..oO(Johnny BeGood)

>When a user enters an Apostrophe into a text area field on a form, i.e.
didn't, it mucks with odbc as follows

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression ''didn't',

This not only breaks the query, but allows an attacker to inject
arbitrary malicious SQL commands. Not good.

>Whats the best way to handle this, other than not entering the apostrophe.

You _always_ have to make sure that the data entered into a DB can't do
any harm. To achieve that you have to

1) escape all chars that have a special meaning in SQL
or
2) use prepared statements

The second is the preferred, but whether it's available or not depends
on the used DB backend and the interface.

Micha

[Johnny BeGood]

Hi Micha,

Thanks for the reply, can you give me some guidance on

2) use prepared statements

The second is the preferred, but whether it's available or not depends
on the used DB backend and the interface.

I have used

$stmt = odbc_prepare($odbc, "INSERT INTO Tasks (TaskType, Details)
VALUES('$tasktype','$taskdetails');" );

if (!odbc_execute( $stmt))
{
echo odbc_errormsg();
}
odbc_close();

But still come up with the same error, I am using an Access 2002 backend
database, only because I'm forced to.

Cheers

[Michael Fesser]

..oO(Johnny BeGood)

>Thanks for the reply, can you give me some guidance on

>2) use prepared statements

The second is the preferred, but whether it's available or not depends
on the used DB backend and the interface.

I have used

$stmt = odbc_prepare($odbc, "INSERT INTO Tasks (TaskType, Details)
VALUES('$tasktype','$taskdetails');" );

Maybe I should have said "parameterized statement". The purpose of such
statements is to use parameters/placeholders in the query string, which
are replaced with the current values _after_ the statement was prepared.
Your code should look like this (just splitted a bit for legibility):

// query string with 2 placeholders
$query = 'INSERT INTO Tasks (TaskType, Details) VALUES (?, ?)';

// prepare the statement
$stmt = odbc_prepare($odbc, $query);

// pass all parameters in an array and execute the statement
if (!odbc_execute($stmt, array($tasktype, $taskdetails))) {
...
}

HTH
Micha

[Johnny BeGood]

Hi Micha,

If I enter didn''t it works, if I enter didn't it comes back with the same
error as before
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression ''didn't',

This is what I have
$query = 'INSERT INTO Tasks (TaskType, Details) VALUES ($tasktype,
$taskdetails)';
$stmt = odbc_prepare($odbc, $query);
if (!odbc_execute($stmt, array($tasktype, $taskdetails)))
{
echo odbc_errormsg();
}

Where am I going wrong (:

Cheers

"Michael Fesser" <ne*****@gmx.dewrote in message
news:62********************************@4ax.com...

// query string with 2 placeholders
$query = 'INSERT INTO Tasks (TaskType, Details) VALUES (?, ?)'; - this
throws a field count error, silly me?

// prepare the statement
$stmt = odbc_prepare($odbc, $query);

// pass all parameters in an array and execute the statement
if (!odbc_execute($stmt, array($tasktype, $taskdetails))) {
...
}

HTH
Micha

[Jerry Stuckle]

Johnny BeGood wrote:

"Michael Fesser" <ne*****@gmx.dewrote in message
news:62********************************@4ax.com...

>// query string with 2 placeholders
$query = 'INSERT INTO Tasks (TaskType, Details) VALUES (?, ?)'; - this
throws a field count error, silly me?

// prepare the statement
$stmt = odbc_prepare($odbc, $query);

// pass all parameters in an array and execute the statement
if (!odbc_execute($stmt, array($tasktype, $taskdetails))) {
...
}

HTH
Micha

Hi Micha,

If I enter didn''t it works, if I enter didn't it comes back with the
same error as before
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression ''didn't',

This is what I have
$query = 'INSERT INTO Tasks (TaskType, Details) VALUES ($tasktype,
$taskdetails)';
$stmt = odbc_prepare($odbc, $query);
if (!odbc_execute($stmt, array($tasktype, $taskdetails)))
{
echo odbc_errormsg();
}

Where am I going wrong (:

Cheers

(Top posting fixed)

The single quote is defined by SQL as the separator (enclosing
character) for string values. The string you're trying to insert, by
the time it gets to SQL, would be:

'It didn't work'

Note the mismatched single quotes. Some languages, like C and PHP,
escape special characters like this with a backslash, i.e.

'It didn\'t work'.

SQL does it a little differently - you double the apostrophe, so it
comes out as:

'It didn''t work'

And this does work just fine.

P.S. Please don't top post. Thanks.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Johny Begood]

Hi Jerry,

Thanks for that, how do I get it that the user doesn't have to do anything
other that type in the word?
How can I escape special characters at data entry, the site users will be
typical users, they wont care!

Cheers

PS what is top posting? I've been slapped a few times for this, but don't
fully understand
"Jerry Stuckle" <js*******@attglobal.netwrote in message
news:Qr******************************@comcast.com. ..

Johnny BeGood wrote:

"Michael Fesser" <ne*****@gmx.dewrote in message
news:62********************************@4ax.com...

// query string with 2 placeholders
$query = 'INSERT INTO Tasks (TaskType, Details) VALUES (?, ?)'; - this
throws a field count error, silly me?

// prepare the statement
$stmt = odbc_prepare($odbc, $query);

// pass all parameters in an array and execute the statement
if (!odbc_execute($stmt, array($tasktype, $taskdetails))) {
...
}

HTH
Micha

Hi Micha,
>
If I enter didn''t it works, if I enter didn't it comes back with the
same error as before
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression ''didn't',
>
This is what I have
$query = 'INSERT INTO Tasks (TaskType, Details) VALUES ($tasktype,
$taskdetails)';
$stmt = odbc_prepare($odbc, $query);
if (!odbc_execute($stmt, array($tasktype, $taskdetails)))
{
echo odbc_errormsg();
}
>
Where am I going wrong (:
>
Cheers
>

(Top posting fixed)

The single quote is defined by SQL as the separator (enclosing
character) for string values. The string you're trying to insert, by
the time it gets to SQL, would be:

'It didn't work'

Note the mismatched single quotes. Some languages, like C and PHP,
escape special characters like this with a backslash, i.e.

'It didn\'t work'.

SQL does it a little differently - you double the apostrophe, so it
comes out as:

'It didn''t work'

And this does work just fine.

P.S. Please don't top post. Thanks.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Schraalhans Keukenmeester]

At Mon, 14 May 2007 07:31:26 +0100, Johny Begood let his monkeys type:

>
PS what is top posting? I've been slapped a few times for this, but don't
fully understand

Top posting is what you'd normally do when replying to an email: you
put your reply ABOVE the quoted message.

In usenet (newsgroups) it is a widely accepted standard to put your reply
BELOW the quoted text. This makes it easier for people to follow the
thread order. Whilst it isn't THAT big a deal for someone using a
newsreader to track back in the thread, others may be viewing the message
in a web browser, without the full thread. For them, a top-posted reply
has no meaning, so they'd have to scroll down first, read the previously
contributed bits and then scroll back up again.

Outlook/Outlook Express unfortunately defaults news-replies to be written
above the quoted text. This setting can easily be changed however.

It is also acceptable to break your reply in pieces, each part
immediately following *below* the part of the quoted text it relates to.
Another thing to keep in mind: only quote what's relevant to your reply.
Cut out the irrelevant bits to keep the message short and to the point.
(e.g.: You may have noticed I cut out the part of your question I am not
replying to)

If you're interested (one might argue every usenetter should be) look up
'usenet etiquette' or usenetiquette' for an elaboration on what (not) to
do using newsgroups.

Many people will 'fix' wrongly ordered replies and ask the person in
question (nicely) to quit top-posting in the future. Simply because many,
especially google-groups users, who often haven't even got a clue they are
in fact entering stuff in a newsgroups (Google has a way of making it all
look like 'their own'), aren't aware of that fact.

And then there are some that ask not so nicely. It's just like with real
people. A few will even PLONK the author altogether if he/she keeps
ignoring the hints. (Google: plonk)

HTH
Sh.

[Toby A Inkster]

Johnny BeGood wrote:

$query = 'INSERT INTO Tasks (TaskType, Details) VALUES ($tasktype,
$taskdetails)';
$stmt = odbc_prepare($odbc, $query);
if (!odbc_execute($stmt, array($tasktype, $taskdetails)))
{
echo odbc_errormsg();
}

As you've already been told, you are using prepared queries incorrectly.

Try:

$query = 'INSERT INTO Tasks (TaskType, Details) VALUES (?, ?);';
$stmt = odbc_prepare($odbc, $query);
if (!odbc_execute($stmt, array($tasktype, $taskdetails)))
{
echo odbc_errormsg();
}

--
Toby A Inkster BSc (Hons) ARCS
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python/Apache/Linux

[Rami Elomaa]

"Johnny BeGood" <jb*@jbg.netwrote in message
news:U0*******************@news.indigo.ie...

Hi All,

When a user enters an Apostrophe into a text area field on a form, i.e.
didn't, it mucks with odbc as follows

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in query expression ''didn't',

Whats the best way to handle this, other than not entering the apostrophe.
I'm stuck with the current backend database as its part of an existing
application.

Apostrophes are escaped by another apostrophe. Ie. 'didn't' should be
entered as 'didn''t'. At least this is how it works in MS SQL, and I'm
certain that Access has the same syntax.

--
Ra*********@gmail.com

"Good tea. Nice house." -- Worf

[Rami Elomaa]

"Johny Begood" <jb*****@mickey.comwrote in message
news:Qo*******************@news.indigo.ie...

Hi Jerry,

Thanks for that, how do I get it that the user doesn't have to do anything
other that type in the word?
How can I escape special characters at data entry, the site users will be
typical users, they wont care!

str_replace("'","''",$user_input);

--
Ra*********@gmail.com

"Good tea. Nice house." -- Worf

[Jerry Stuckle]

Johny Begood wrote:

"Jerry Stuckle" <js*******@attglobal.netwrote in message
news:Qr******************************@comcast.com. ..

>Johnny BeGood wrote:

>>"Michael Fesser" <ne*****@gmx.dewrote in message
news:62********************************@4ax.com. ..
// query string with 2 placeholders
$query = 'INSERT INTO Tasks (TaskType, Details) VALUES (?, ?)'; - this
throws a field count error, silly me?

// prepare the statement
$stmt = odbc_prepare($odbc, $query);

// pass all parameters in an array and execute the statement
if (!odbc_execute($stmt, array($tasktype, $taskdetails))) {
...
}

HTH
Micha
Hi Micha,

If I enter didn''t it works, if I enter didn't it comes back with the
same error as before
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression ''didn't',

This is what I have
$query = 'INSERT INTO Tasks (TaskType, Details) VALUES ($tasktype,
$taskdetails)';
$stmt = odbc_prepare($odbc, $query);
if (!odbc_execute($stmt, array($tasktype, $taskdetails)))
{
echo odbc_errormsg();
}

Where am I going wrong (:

Cheers

(Top posting fixed)

The single quote is defined by SQL as the separator (enclosing
character) for string values. The string you're trying to insert, by
the time it gets to SQL, would be:

'It didn't work'

Note the mismatched single quotes. Some languages, like C and PHP,
escape special characters like this with a backslash, i.e.

'It didn\'t work'.

SQL does it a little differently - you double the apostrophe, so it
comes out as:

'It didn''t work'

And this does work just fine.

P.S. Please don't top post. Thanks.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Hi Jerry,

Thanks for that, how do I get it that the user doesn't have to do
anything other that type in the word?
How can I escape special characters at data entry, the site users will
be typical users, they wont care!

Cheers

PS what is top posting? I've been slapped a few times for this, but
don't fully understand

(Top posting fixed)

As others have indicated, you can use prepared statements with
parameters, or you can modify the string just before you insert it into
the database, replacing "'" with "''".

And top posting is placing your response at the top of the message,
instead of the bottom (like this), or (as is usually the case with
longer messages), at appropriate places within the previous message.

A. Because it upsets the flow of the discussion.
Q. Why is it so bad?
A. Top posting.
Q. What is the most annoying thing on usenet?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Toby A Inkster]

Rami Elomaa wrote:

Apostrophes are escaped by another apostrophe. Ie. 'didn't' should be
entered as 'didn''t'. At least this is how it works in MS SQL, and I'm
certain that Access has the same syntax.

Yep, that's the SQL standard method of escaping quotes. Some databases
also support backslashed escapes ('didn\'t') though that's non-standard,
so it's unwise to rely on such support.

--
Toby A Inkster BSc (Hons) ARCS
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python/Apache/Linux