Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,446
    Thanks
    275
    Thanked 32 Times in 31 Posts

    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.
    Last edited by durangod; 10-15-2012 at 03:58 PM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by durangod View Post
    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.

  • #3
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,446
    Thanks
    275
    Thanked 32 Times in 31 Posts
    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.




    PHP Code:

    //added by dave to remove all connections
     
    if(isset($_POST['removecon'])){
    $qry 'TRUNCATE TABLE '.$GLOBALS['fc_config']['db']['pref'].'connections';
    $stmt1 = new Statement($qry69);
    $rs1 $stmt1->process();
    }
    //end if

    //individual connections

    if(isset($_GET['remicon'])){
    $rid $_GET['remicon'];
    $query 'DELETE FROM '.$GLOBALS['fc_config']['db']['pref'].'connections WHERE id='.$rid;
    $stmt1 = new Statement($query,69);
    $rs1 $stmt1->process();
    }
    //end if

    //end remove connections mod 

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #6
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,446
    Thanks
    275
    Thanked 32 Times in 31 Posts
    @ 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

    PHP Code:
    $query 'DELETE FROM '.$GLOBALS['fc_config']['db']['pref'].'connections WHERE id='.$rid

    and 
    the echo of $rid is 3a226b173e49ffd3d32d416174455354 
    here is the echo of $query

    PHP Code:
    DELETE FROM chat_ connections WHERE id=3a226b173e49ffd3d32d416174455354 
    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

    PHP Code:
    $query "DELETE FROM ".$GLOBALS['fc_config']['db']['pref']."connections WHERE id="."$rid"
    Last edited by durangod; 10-15-2012 at 03:46 PM.

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Right, which is wrong.
    PHP Code:
     $query 'DELETE FROM ' $GLOBALS['fc_config']['db']['pref'] . 'connections WHERE id="'  .$rid '"'
    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).

  • Users who have thanked Fou-Lu for this post:

    durangod (10-15-2012)

  • #8
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,446
    Thanks
    275
    Thanked 32 Times in 31 Posts
    Thats what i was trying to get at so your query echos as this

    PHP Code:
    DELETE FROM chat_connections WHERE id="3a226b173e49ffd3d32d416174455354" 
    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....


    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...

  • #9
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,446
    Thanks
    275
    Thanked 32 Times in 31 Posts
    One last question so i can learn... what is this manipulation called?

    PHP Code:
    id="'  .$rid . '"
    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.

  • #10
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by durangod View Post
    @ 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 deletes the row.
    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.

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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 :/

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,206
    Thanks
    80
    Thanked 4,566 Times in 4,530 Posts
    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:

    Code:
    $tablename = $GLOBALS['fc_config']['db']['pref'] . "connections";
    
    $query = "DELETE FROM $tablename WHERE id = '$rid' ";
    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.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,206
    Thanks
    80
    Thanked 4,566 Times in 4,530 Posts
    And FouLu: Notice that I even put spaces in id = '...' <grin/>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #14
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Lols
    I hadn't realized that SQL's ANSI standard was to use single quotations. I figured either or.
    Learn something new every day!

  • #15
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,446
    Thanks
    275
    Thanked 32 Times in 31 Posts
    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...


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •