Announcement

Collapse
No announcement yet.

deleting a record in mysql based on a string

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    deleting a record in mysql based on a string

    I have a delete function working right up until the field value has a character like a #. Then the record won't be deleted.

    The field is defined as char(50).

    'DELETE FROM ' $ g.Store_Table_Prefix $ 'tblname WHERE strdata = ?'

    test 1 is deleted
    test #1 is not deleted

    And, it makes sense that I can't find the record and pull into my form to do an update.

    I actually don't care either way to use or invalidate special character. But, using isalnum() doesn't like spaces either. I don't think that would be a problem either. But, seems like I should just fix the query instead.

    Do I need to escape those characters for this to work? If so, is there a way to automatically escape chars I don't know is being used in the query?

    Or, is there something I am missing?

    BTW: I am not current using any indexing in this table. I was planning on recoding to use a unique index on this field.

    Thanks.

    Scott
    Last edited by ids; 05-28-14, 10:47 AM.
    Need to offer Shipping Insurance?
    Interactive Design Solutions https://www.myids.net
    MivaMerchant Business Partner | Certified MivaMerchant Web Developer
    Competitive Rates, Custom Modules and Integrations, Store Integration
    AutoBaskets|Advanced Waitlist Integration|Ask about Shipping Insurance Integration
    My T-shirt Collection is mostly MivaCon T-shirts!!

    #2
    Re: deleting a record in mysql based on a string

    Variable values that contain hash characters (#) in SQL don't need to be quoted, but literals and field names probably do (according to MySQL docs).

    What MySQL error are you getting? Have you tried it from something like PHPMyAdmin (to rule out Miva Script errors)?
    Gordon Currie
    Phosphor Media - "Your Success is our Business"

    Improve Your Customer Service | Get MORE Customers | Edit Any Document Easily | Free Modules | Follow Us on Facebook
    phosphormedia.com

    Comment


      #3
      Re: deleting a record in mysql based on a string

      Haven't tried yet in the console, but that is a good idea.

      No errors at runtime.

      How does the literal get quoted?

      This is what I have:

      HTML Code:
      <MvQUERY NAME	= "Merchant"
      			 QUERY	= "{ 'DELETE FROM ' $ g.Store_Table_Prefix $ 'tblname WHERE strdata = ?' }"
      			 FIELDS	= "l.delvalue">
      I have tried this yet, but would be this? Where the ? is "?" instead?

      HTML Code:
      <MvQUERY NAME	= "Merchant"
      			 QUERY	= "{ 'DELETE FROM ' $ g.Store_Table_Prefix $ 'tblname WHERE strdata = "?"' }"
      			 FIELDS	= "l.delvalue">
      Scott
      Need to offer Shipping Insurance?
      Interactive Design Solutions https://www.myids.net
      MivaMerchant Business Partner | Certified MivaMerchant Web Developer
      Competitive Rates, Custom Modules and Integrations, Store Integration
      AutoBaskets|Advanced Waitlist Integration|Ask about Shipping Insurance Integration
      My T-shirt Collection is mostly MivaCon T-shirts!!

      Comment


        #4
        Re: deleting a record in mysql based on a string

        Scott:

        Sorry, I meant that if the field NAME had a # in it, it would need to be quoted/escaped.

        I see no reason why a variable value containing a # does not work. MySQL doesn't preclude it.

        STANDARDOUTPUTLEVEL="" in this function? Grasping at straws, you should try it in PHPMyAdmin...
        Gordon Currie
        Phosphor Media - "Your Success is our Business"

        Improve Your Customer Service | Get MORE Customers | Edit Any Document Easily | Free Modules | Follow Us on Facebook
        phosphormedia.com

        Comment


          #5
          Re: deleting a record in mysql based on a string

          p.s. it's best to use a unique key to delete. Are you certain that the value of the strdata field is unique? If there were two records with that value, then that would explain why it is not being deleted (althought you would get an error).
          Gordon Currie
          Phosphor Media - "Your Success is our Business"

          Improve Your Customer Service | Get MORE Customers | Edit Any Document Easily | Free Modules | Follow Us on Facebook
          phosphormedia.com

          Comment


            #6
            Re: deleting a record in mysql based on a string

            Solved it. Turns out, since I was passing the value via url, that the url was malformed.

            Scott
            Need to offer Shipping Insurance?
            Interactive Design Solutions https://www.myids.net
            MivaMerchant Business Partner | Certified MivaMerchant Web Developer
            Competitive Rates, Custom Modules and Integrations, Store Integration
            AutoBaskets|Advanced Waitlist Integration|Ask about Shipping Insurance Integration
            My T-shirt Collection is mostly MivaCon T-shirts!!

            Comment


              #7
              Re: deleting a record in mysql based on a string

              Excuse me while I cringe from an uncontrolled case of hebbie-jebbies...

              You're passing an unfiltered, unsanitized, and otherwise uncontrolled value from a URL into your SQL database? Specifically into a DELETE statement?

              Please tell me I'm missing something from this scenario.

              Comment


                #8
                Re: deleting a record in mysql based on a string

                Yes, you don't have the whole picture.
                Need to offer Shipping Insurance?
                Interactive Design Solutions https://www.myids.net
                MivaMerchant Business Partner | Certified MivaMerchant Web Developer
                Competitive Rates, Custom Modules and Integrations, Store Integration
                AutoBaskets|Advanced Waitlist Integration|Ask about Shipping Insurance Integration
                My T-shirt Collection is mostly MivaCon T-shirts!!

                Comment

                Working...
                X