------=_NextPart_000_005A_01C4837B.7BEA8680
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

I'm having some inconsistency with mysql insert queries when there is a
single quote involved.

Example: A new member register's with the name of Jason O'Neal. There are no
addslashes in the code, and the user is entered into the table correctly.

Insert into members (name) values ('$_POST[name]');

Now the user submits a trouble ticket from within the site. The process is
to select the name from the members table and insert it along with the
ticket, into the tickets table. When this happens, I get an error on the
insert.

Select name from members where id = $_SESSION[uid];

Insert into tickets (name,problem) values ('$row[name]','$_POST[problem]');

Now I am forced to use addslashes to make it work, as well for the problem
that they submit.

What is the difference? It seems that if it works one place, then it should
work every where?

Or would it matter that name is not a key in the members table but is in the
tickets, or Vice Versa?

This is really bugging me.

Thank You,
Chad Stalvey
Dynamic Solutions
www.dyn3.com
(850)-508-0369
chad [at] dyn3.com


------=_NextPart_000_005A_01C4837B.7BEA8680--

Hi Chad, please see below

"Chad Stalvey" <chad [at] dyn3.com> wrote in message
news:200408161420.i7GEKsCT058166 [at] ns1.dyn3.com...
> I'm having some inconsistency with mysql insert queries when there is a
> single quote involved.
>
> Example: A new member register's with the name of Jason O'Neal. There are
no
> addslashes in the code, and the user is entered into the table correctly.
>
> Insert into members (name) values ('$_POST[name]');

You don't need the quotes here because you want to insert the value of
$_POST['name'] and not the string '$_POST[name]'. Change the line to:

Insert into members (name) values ($_POST['name']);

>
> Now the user submits a trouble ticket from within the site. The process is
> to select the name from the members table and insert it along with the
> ticket, into the tickets table. When this happens, I get an error on the
> insert.
>
> Select name from members where id = $_SESSION[uid];
>
> Insert into tickets (name,problem) values
('$row[name]','$_POST[problem]');

You are always omitting the quotes around your array keys! Change it to:

Select name from members where id = $_SESSION['uid'];
and
Insert into tickets (name,problem) values ($row['name'], $_POST['problem']);

>
> Now I am forced to use addslashes to make it work, as well for the problem
> that they submit.
>
> What is the difference? It seems that if it works one place, then it
should
> work every where?
>
> Or would it matter that name is not a key in the members table but is in
the
> tickets, or Vice Versa?
>
> This is really bugging me.

Please try if those changes solve your problem. Whenever one of your values
will contain a single quote you will get an SQL error - so use addslashes()
or (better) mysql_real_escape_string() on all insert values.

Hope this helps.

Regards, Torsten Roehr

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Ok. It seems that a $_POST value comes over with the escaped single quote as
in O\'Neal. So why does it not preserve that escape when pulling a value
from a table field, and inserting it back into another table field? When I
pull it out and insert it back in it is simply O'Neal.

-----Original Message-----
From: Torsten Roehr [mailto:roehr [at] zilleon.com]
Sent: Monday, August 16, 2004 9:44 AM
To: php-db [at] lists.php.net
Subject: [PHP-DB] Re: Basic MySQL Query Question

Hi Chad, please see below

"Chad Stalvey" <chad [at] dyn3.com> wrote in message
news:200408161420.i7GEKsCT058166 [at] ns1.dyn3.com...
> I'm having some inconsistency with mysql insert queries when there is a
> single quote involved.
>
> Example: A new member register's with the name of Jason O'Neal. There are
no
> addslashes in the code, and the user is entered into the table correctly.
>
> Insert into members (name) values ('$_POST[name]');

You don't need the quotes here because you want to insert the value of
$_POST['name'] and not the string '$_POST[name]'. Change the line to:

Insert into members (name) values ($_POST['name']);

>
> Now the user submits a trouble ticket from within the site. The process is
> to select the name from the members table and insert it along with the
> ticket, into the tickets table. When this happens, I get an error on the
> insert.
>
> Select name from members where id = $_SESSION[uid];
>
> Insert into tickets (name,problem) values
('$row[name]','$_POST[problem]');

You are always omitting the quotes around your array keys! Change it to:

Select name from members where id = $_SESSION['uid'];
and
Insert into tickets (name,problem) values ($row['name'], $_POST['problem']);

>
> Now I am forced to use addslashes to make it work, as well for the problem
> that they submit.
>
> What is the difference? It seems that if it works one place, then it
should
> work every where?
>
> Or would it matter that name is not a key in the members table but is in
the
> tickets, or Vice Versa?
>
> This is really bugging me.

Please try if those changes solve your problem. Whenever one of your values
will contain a single quote you will get an SQL error - so use addslashes()
or (better) mysql_real_escape_string() on all insert values.

Hope this helps.

Regards, Torsten Roehr

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

"Chad Stalvey" <chad [at] dyn3.com> wrote in message
news:200408161455.i7GEtLCT058310 [at] ns1.dyn3.com...
> Ok. It seems that a $_POST value comes over with the escaped single quote
as
> in O\'Neal. So why does it not preserve that escape when pulling a value
> from a table field, and inserting it back into another table field? When I
> pull it out and insert it back in it is simply O'Neal.

Because the escape quotes are not stored in the database. Otherwise all data
would have to be "cleaned" when fetching it from the DB. It's the
application's job to sanitize all user submitted data. So apply
mysql_real_escape_string() to all submitted values and turn off magic_quotes
in your php.ini.

Regards, Torsten

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Is it just me or is this a very bad thing from a security standpoint? It
seems to me that user input should always be filtered before use. Otherwise
there's nothing stopping a hacker from embedding sql into the value of the
name variable.

> -----Original Message-----
> Insert into members (name) values ($_POST['name']);

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

------_=_NextPart_001_01C48401.EF698E8C
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

That's what escapeshellcmd() is for - never ever trust user data. At =
minimum, I would always use addslashes() or the new =
mysql_real_escape_string() around every bit of user data if it's =
touching the db. At minimum, and in lieu of data validation that is =
really checking what the user entered (alpha plus spaces, hyphen, =
period, apostrophe only) I would do this:

$query =3D "Insert into members (name) values =
('".addslashes($_POST['name'])."')";



-----Original Message-----
From: Ed Lazor [mailto:Ed.Lazor [at] d20News.com]
Sent: Mon 8/16/2004 10:06 AM
To: 'Torsten Roehr'; php-db [at] lists.php.net
Cc:=09
Subject: RE: [PHP-DB] Re: Basic MySQL Query Question
Is it just me or is this a very bad thing from a security standpoint? =
It
seems to me that user input should always be filtered before use. =
Otherwise
there's nothing stopping a hacker from embedding sql into the value of =
the
name variable.

> -----Original Message-----
> Insert into members (name) values ($_POST['name']);





------_=_NextPart_001_01C48401.EF698E8C--

thks

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php