What am I doing wrong?

Hi all,

I am having a problem with one of my scripts that when the data
contains an apostrophe in any of the fields it is causing a query
error with mysql, however the data is still being inserted into the
table. I am using addslashes to add the needed backslash prior to the
apostrophes, and when printing the query that is used, it looks like
the following:

INSERT INTO dbo_SC_Data
(Requestor,Work_Order_Type,Subject,Date_Request,Date_Complet ed,Summary_Request,Notify,comp_time_hrs)
VALUES ('ITGENERAL','Alchemy','test
\'s','2007-08-08','0000-00-00','this is getting annoying...the query
works when done directly to mysql client\'s, but will not work when
sent through php...',0,0)

The message I receive on the page is:

Query failed: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near 's' AND Date_Request='2007-08-08'' at line 1

If I copy the query exactly as printed on the page, and paste directly
into the mysql client, the query is processed without any errors. The
php code is following:

$subject = addslashes($subject);
$sreq = addslashes($sreq);
$scomp = addslashes($scomp);
$qry = "INSERT INTO dbo_SC_Data
(Requestor,Work_Order_Type,Subject,Date_Request,Date_Complet ed,Summary_Request,Notify,comp_time_hrs)
VALUES ('".$requestor."','".$wotype."','".$subject."','".
$mreqdate."','".$compdate."','".$sreq."',0,0)";

The above code is in a function that ends up calling a seperate
function to connect to mysql and run the query. I am posting this in
the php group, since the query when copied to the mysql works
properly, and so I am thinking it is some issue with the php on my
system.

Thanks,
david

Re: What am I doing wrong?

Wait, i think I know what the problem is...I just realized it is
running another query afterward to get the record for the previously
submitted record, that seems to be the one causing the error.

Re: What am I doing wrong?

dkruger wrote:
> Hi all,
>
> I am having a problem with one of my scripts that when the data
> contains an apostrophe in any of the fields it is causing a query
> error with mysql, however the data is still being inserted into the
> table. I am using addslashes to add the needed backslash prior to the
> apostrophes, and when printing the query that is used, it looks like
> the following:
>
> INSERT INTO dbo_SC_Data
> (Requestor,Work_Order_Type,Subject,Date_Request,Date_Complet ed,Summary_Request,Notify,comp_time_hrs)
> VALUES ('ITGENERAL','Alchemy','test
> \'s','2007-08-08','0000-00-00','this is getting annoying...the query
> works when done directly to mysql client\'s, but will not work when
> sent through php...',0,0)
>
> The message I receive on the page is:
>
> Query failed: You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to
> use near 's' AND Date_Request='2007-08-08'' at line 1
>
> If I copy the query exactly as printed on the page, and paste directly
> into the mysql client, the query is processed without any errors. The
> php code is following:
>
> $subject = addslashes($subject);
> $sreq = addslashes($sreq);
> $scomp = addslashes($scomp);
> $qry = "INSERT INTO dbo_SC_Data
> (Requestor,Work_Order_Type,Subject,Date_Request,Date_Complet ed,Summary_Request,Notify,comp_time_hrs)
> VALUES ('".$requestor."','".$wotype."','".$subject."','".
> $mreqdate."','".$compdate."','".$sreq."',0,0)";
>
> The above code is in a function that ends up calling a seperate
> function to connect to mysql and run the query. I am posting this in
> the php group, since the query when copied to the mysql works
> properly, and so I am thinking it is some issue with the php on my
> system.
>
> Thanks,
> david
>

Use mysql_real_escape_string() instead of addslashes(). That's what
it's there for.

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

Re: What am I doing wrong?

On Aug 8, 10:04 am, Jerry Stuckle wrote:
> dkruger wrote:
> > Hi all,
>
> > I am having a problem with one of my scripts that when the data
> > contains an apostrophe in any of the fields it is causing a query
> > error with mysql, however the data is still being inserted into the
> > table. I am using addslashes to add the needed backslash prior to the
> > apostrophes, and when printing the query that is used, it looks like
> > the following:
>
> > INSERT INTO dbo_SC_Data
> > (Requestor,Work_Order_Type,Subject,Date_Request,Date_Complet ed,Summary_=
Requ=ADest,Notify,comp_time_hrs)
> > VALUES ('ITGENERAL','Alchemy','test
> > \'s','2007-08-08','0000-00-00','this is getting annoying...the query
> > works when done directly to mysql client\'s, but will not work when
> > sent through php...',0,0)
>
> > The message I receive on the page is:
>
> > Query failed: You have an error in your SQL syntax; check the manual
> > that corresponds to your MySQL server version for the right syntax to
> > use near 's' AND Date_Request=3D'2007-08-08'' at line 1
>
> > If I copy the query exactly as printed on the page, and paste directly
> > into the mysql client, the query is processed without any errors. The
> > php code is following:
>
> > $subject =3D addslashes($subject);
> > $sreq =3D addslashes($sreq);
> > $scomp =3D addslashes($scomp);
> > $qry =3D "INSERT INTO dbo_SC_Data
> > (Requestor,Work_Order_Type,Subject,Date_Request,Date_Complet ed,Summary_=
Requ=ADest,Notify,comp_time_hrs)
> > VALUES ('".$requestor."','".$wotype."','".$subject."','".
> > $mreqdate."','".$compdate."','".$sreq."',0,0)";
>
> > The above code is in a function that ends up calling a seperate
> > function to connect to mysql and run the query. I am posting this in
> > the php group, since the query when copied to the mysql works
> > properly, and so I am thinking it is some issue with the php on my
> > system.
>
> > Thanks,
> > david
>
> Use mysql_real_escape_string() instead of addslashes(). That's what
> it's there for.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

Thanks for the tip, I have resolved the problem, but the reason
mysql_real_escape_string() is not being used, is the query statement
is generated prior to making a connection to the mysql server, and if
I understand correctly mysql_real_escape_string has to be run after
establishing a connection to mysql, which in my situation makes it not
an option. Since not all of my code is shown previously, there would
be no way you would have known that was why addslashes was being used
instead.

Thanks,
David

Re: What am I doing wrong?

..oO(dkruger)

>Thanks for the tip, I have resolved the problem, but the reason
>mysql_real_escape_string() is not being used, is the query statement
>is generated prior to making a connection to the mysql server, and if
>I understand correctly mysql_real_escape_string has to be run after
>establishing a connection to mysql, which in my situation makes it not
>an option. Since not all of my code is shown previously, there would
>be no way you would have known that was why addslashes was being used
>instead.

addslashes() is _not_ an appropriate way to prevent SQL injection! If
you can't do proper escaping, then your code structure is ... at least
suboptimal (I don't want to call it broken, but it is somewhat). Is
there any particular reason why you can't open a DB connection first?
Usually that's done at the beginning of a script, if there's any DB
operation to be done.

Of course even better would be to use PDO and prepared statements, but
even then you would have to open a connection first, before performing
any action. That's how it should be and how it works.

Another question, just out of curiosity - in your second posting you
wrote:

>Wait, i think I know what the problem is...I just realized it is
>running another query afterward to get the record for the previously
>submitted record, that seems to be the one causing the error.

May I ask how you get the previously inserted record in your second
query? Just want to be sure, because there's a right way and a wrong way
for doing that ...

Micha

Re: What am I doing wrong?

On Aug 8, 12:41 pm, Michael Fesser wrote:
> .oO(dkruger)
>
> >Thanks for the tip, I have resolved the problem, but the reason
> >mysql_real_escape_string() is not being used, is the query statement
> >is generated prior to making a connection to the mysql server, and if
> >I understand correctly mysql_real_escape_string has to be run after
> >establishing a connection to mysql, which in my situation makes it not
> >an option. Since not all of my code is shown previously, there would
> >be no way you would have known that was why addslashes was being used
> >instead.
>
> addslashes() is _not_ an appropriate way to prevent SQL injection! If
> you can't do proper escaping, then your code structure is ... at least
> suboptimal (I don't want to call it broken, but it is somewhat). Is
> there any particular reason why you can't open a DB connection first?
> Usually that's done at the beginning of a script, if there's any DB
> operation to be done.
>
> Of course even better would be to use PDO and prepared statements, but
> even then you would have to open a connection first, before performing
> any action. That's how it should be and how it works.
>
> Another question, just out of curiosity - in your second posting you
> wrote:
>
> >Wait, i think I know what the problem is...I just realized it is
> >running another query afterward to get the record for the previously
> >submitted record, that seems to be the one causing the error.
>
> May I ask how you get the previously inserted record in your second
> query? Just want to be sure, because there's a right way and a wrong way
> for doing that ...
>
> Micha

I understand that addslashes is not an appropriate way to prevent the
SQL injections for occuring, from the way it looks,
mysql_real_escape_string really only seems to replace a few other
characters in the passed string...I could be and probably am wrong
with how it works and prevents the injections, but what happens in my
code and with the code example above, is it executes a function that
receives the query string, and database, that function then connects
to mysql, runs the query, disconnects from mysql and returns any data
in an array as a result. If I were going to add the additional
character replacements that addslashes does not do and
mysql_real_escape_string does, couldn't I just use str_replace to
replace each in the function that receives the query?

For getting the data submitted, I am sure is probably a wrong way of
doing it, but it works. All that does, is returns the latest record
id for the record matching the Requestor, subject, and Date_Request
fields. That query was the one that was causing the error that I
refer to and have corrected now.

Re: What am I doing wrong?

dkruger wrote:
> On Aug 8, 12:41 pm, Michael Fesser wrote:
>> .oO(dkruger)
>>
>>> Thanks for the tip, I have resolved the problem, but the reason
>>> mysql_real_escape_string() is not being used, is the query statement
>>> is generated prior to making a connection to the mysql server, and if
>>> I understand correctly mysql_real_escape_string has to be run after
>>> establishing a connection to mysql, which in my situation makes it not
>>> an option. Since not all of my code is shown previously, there would
>>> be no way you would have known that was why addslashes was being used
>>> instead.
>> addslashes() is _not_ an appropriate way to prevent SQL injection! If
>> you can't do proper escaping, then your code structure is ... at least
>> suboptimal (I don't want to call it broken, but it is somewhat). Is
>> there any particular reason why you can't open a DB connection first?
>> Usually that's done at the beginning of a script, if there's any DB
>> operation to be done.
>>
>> Of course even better would be to use PDO and prepared statements, but
>> even then you would have to open a connection first, before performing
>> any action. That's how it should be and how it works.
>>
>> Another question, just out of curiosity - in your second posting you
>> wrote:
>>
>>> Wait, i think I know what the problem is...I just realized it is
>>> running another query afterward to get the record for the previously
>>> submitted record, that seems to be the one causing the error.
>> May I ask how you get the previously inserted record in your second
>> query? Just want to be sure, because there's a right way and a wrong way
>> for doing that ...
>>
>> Micha
>
> I understand that addslashes is not an appropriate way to prevent the
> SQL injections for occuring, from the way it looks,
> mysql_real_escape_string really only seems to replace a few other
> characters in the passed string...I could be and probably am wrong
> with how it works and prevents the injections, but what happens in my
> code and with the code example above, is it executes a function that
> receives the query string, and database, that function then connects
> to mysql, runs the query, disconnects from mysql and returns any data
> in an array as a result. If I were going to add the additional
> character replacements that addslashes does not do and
> mysql_real_escape_string does, couldn't I just use str_replace to
> replace each in the function that receives the query?
>
> For getting the data submitted, I am sure is probably a wrong way of
> doing it, but it works. All that does, is returns the latest record
> id for the record matching the Requestor, subject, and Date_Request
> fields. That query was the one that was causing the error that I
> refer to and have corrected now.
>

mysql_real_escape_string() escapes characters based on the current
charset being used (which is why it needs the connection). That way if
you need to change your charset you don't need to change your code.

Simply make the connection sooner and do it the correct way.

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

Re: What am I doing wrong?

On 08.08.2007 20:16 dkruger wrote:
> I understand that addslashes is not an appropriate way to prevent the
> SQL injections for occuring, from the way it looks,
> mysql_real_escape_string really only seems to replace a few other
> characters in the passed string...

Unless you're using non-unicode multibyte character sets like GBK,
addslashes is a sufficient protection against injections.
real_escape_string offers additional, non-essential service (like
escaping newlines) and is aware of multibyte characters (but that was
broken in mysql prior to 5.0.22 IIRC).


--
gosha bine

makrell ~ http://www.tagarga.com/blok/makrell
php done right ;) http://code.google.com/p/pihipi