db delete row permission
 

Hi i did some code changes to my admin area so that i had the option of either truncating the table all together (which i used to do thru phpMyAdmin all the time once a day) or deleting individual rows.

Problem is that my truncate works fine but my individual row del query does nothing. I know the code is correct because as a test i even used a query that i know worked but just changed the value of rowid.

I know the id has value, i know the variable has value. That is all correct.

So that leaves me to believe that for some reason i may have permission to truncate but not permission for row deletion. Im not even sure this is possible thats why im asking.

Do i need to set permissions in php for this or is it a phpMyAdmin deal. Im sort of lost on this one, ok fine not "sort of" but totally :). I dont remember running into this before.

Just looking for a starting point in my discovery here lol

I forgot to add the query does work in phpMyAdmin.

If the query works in phpmyadmin then there are no permissions problems. the error at that point will be something in your php code.

And that will be difficult to help with at this point since you don't show us the DELETE statement.

hi thanks here are they are, i cant find anything wrong with them. Truncate works, but delete does not.

no errors and im using E_ALL and i echoed $rid and it has value, i dont know why they set up an id as var char but the rid is something like this

30ed7b13c560475621c217191fc20216

at first i ran intval($rid) but then i saw it was varchar so then i ran escape on it before the query ran but then i took that off i thought maybe it was preventing it from executing some how.

You just said $rid was a string, not a number. You need to wrap that where clause field comparison with quotations then. Errors with E_ALL will mean nothing if whatever this statement class is doing doesn't issue anything on a failure. So this would indicate to me that the Statement class needs work to determine if it failed.

look up mysql_error in the php manual and use it accordingly to print out error messages caused by mysql errors.

also echo out the value of $query so you can see the actual string that is being passed to mysql.

what value are you passing to $rid? looking at a value of 30ed7b13c560475621c217191fc20216 it is most likely hashed value of SHA1 so you might have to run that hash against the input value.

@ guelphdad - it does not make sense there is nothing special about that table that you would have to convert anything in order to del a row. Lets say i wanted to delete a user with the sha1 password of some encrypted value, it would not matter, all i have to do is say delete from table where password = "the encrypted value" hard coded and it delets the row.

So even if it is a sha1 value i would not matter.

@Fou-Lu

i tried =".$val." and ="$val" and ='$val' and '.$val.' just to see if i could get something to register and nothing all it gave me was errors if i changed it to anything other than ='.$val;

i even tried WHERE id=3a226b173e49ffd3d32d416174455354;
and WHERE id="3a226b173e49ffd3d32d416174455354" which are both live connection values

all it gives me is whitespace and tstring tconstant errors.


This is the only thing it will accept, but it does not delete the row

here is the echo of $query

yeah i see the space after _ i will fix that..

Update nope still nothing even after fixed the space issue.


what i might have to do is add another field to the table that is int and autoincrement and then do the query on that and see if that works


Another Update i did not get an error with this either just tried it the query echo is the same as above (minus the space)

But also did not delete the row

Right, which is wrong.
Is correct.

Although your variables themselves are incorrect. You'll pull a syntactical error when you attempt to run DELETE FROM chat_ connections. You also shouldn't pull any variables from globals, but at least this way you will trigger E_NOTICE when they don't exist (unlike global).

Thats what i was trying to get at so your query echos as this

which is what we need i just didnt know how to wrap it correctly.


BINGO!!! worked perfectly... thanks so much Fou-Lu you are a godsend....:thumbsup:


I know i should not use globals but its an old script and i dont want to overhaul right now bud, as i maybe end up changing to a dif one anyone. but this will suffice for now... :)

One last question so i can learn... what is this manipulation called?

is it part of concatenation? i noticed there is a space before the second dot is that required? i would like to read about this in the docs if its there and learn how to read this properly, im sure i will run into this again.

Perhaps I wasn't clear then. What I meant was perhaps you were passing the string 'apples' when you needed to pass SHA1('apples') to match what was in the table.

Glad you have it figured out.

your question above about concatenation is correct that is all you are doing. The space after the . does not matter except for readability.

This ^
All about how I like to show my formatting, spaces IMO are required (IMO, not in the parser's opinion). I'd personally use sprintf or binding myself though, but in a concat it's always string - period - space - variable - space - period - string.
Looks like I missed a space up there too :/

But since literal strings in SQL should be enclosed in '...' and not in "..." and since PHP allows embedding variables in "..." strings, the easy way to do that is this:

Yes, I know MySQL allows "..." around literal strings. But that's *NOT* ANSI SQL and so if you want to write mostly portable SQL code you shouldn't do it.

(In ANSI SQL, you use "..." around table and field names to allow non-standard names, the same way MySQL [uniquely among databases] uses `...` backticks.)

And FouLu: Notice that I even put spaces in id = '...' <grin/>

Lols
I hadn't realized that SQL's ANSI standard was to use single quotations. I figured either or.
Learn something new every day!

Well im glad something positive came from my question, yep you learn something new every day wether you really want to or not lol in this business..

@Old Pedant, its so funny that at one time it struck me to split that up as you did, not because of my tech savy but just because i wondered if it would work that way, i did not try it but i wish i had. How about that, even some of my hair brained ideas sometimes actually do work and are not so hair brained after all lol... :)