Announcement

Collapse
No announcement yet.

[CLOSED] Apostrophe in content throws MySQL error in Search Filter

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • [CLOSED] Apostrophe in content throws MySQL error in Search Filter

    Using the auto-complete text field type in the Search module of a grid - if there is an apostrophe in the text (i.e., "Annuals aren't Annual") when trying to run the grid with the filter applied, because there is an apostrophe, the report doesn't run. A MySQL error message is generated because the apostrophe (which is part of the SQL) is an out-of-place character.

    Somehow, this needs to be escaped?

    EX:

    Error
    Error while accessing the database:
    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 't Annual!' order by trainingtitle' at line 1
    select trainingtitle, trainingtitle from mgtraining where uid = 132 and trainingtitle = 'Annuals Aren't Annual!' order by trainingtitle

  • #2
    I add beforeInsert and beforeUpdate validators that use PHP's str_replace() to replace occurrences of apostrophes with escaped ("\") apostrophes. I do that on all fields that don't exclude apostrophes and quotes via an input mask.

    It works for MySQL and SQLite3.

    $str = sc_sql_injection({Comments});
    {Comments} = str_replace('\'', '\\\'', $str);

    It would be nice if the macro did that automatically. Hint. Hint.

    Comment


    • #3
      I appreciate the tip. And yes, it would be nice if the macro did that for us. (Still hinting.)

      Comment


      • #4
        Another interesting function to use is addslashes. See http://www.php.net/manual/en/function.addslashes.php
        Albert Drent
        aducom software netherlands
        scriptcase partner, reseller, support and (turn-key) development
        www.scriptcase.eu / www.scriptcase.nl

        Comment


        • #5
          Another great tip! Many thanks.

          Comment


          • #6
            Originally posted by rjjacob View Post
            Another great tip! Many thanks.
            Hello,

            May I tag the topic as solved?

            regards,
            Bernhard Bernsmann

            Comment


            • #7
              Originally posted by bartho View Post
              Hello,

              May I tag the topic as solved?

              regards,
              Bernhard Bernsmann
              Using the functions will work-around the topic. There are many spots where quotes will generate an issue. I think that SC should escape them always by default
              Albert Drent
              aducom software netherlands
              scriptcase partner, reseller, support and (turn-key) development
              www.scriptcase.eu / www.scriptcase.nl

              Comment


              • #8
                I agree it should be escaped at the core level, not as a user-generated custom function as it breaks the display of data in the application, causing a MySQL error. However, for now, my question has been answered and the topic can be considered closed.

                Comment


                • #9
                  Hello,

                  Thanks for your feedback, I will let our team know.

                  regards,
                  Bernhard Bernsmann

                  Comment


                  • #10
                    Not SOLVED but CLOSED

                    Sorry, but as this thread does not apply a real solution, it is not solved but closed.
                    Albert Drent
                    aducom software netherlands
                    scriptcase partner, reseller, support and (turn-key) development
                    www.scriptcase.eu / www.scriptcase.nl

                    Comment

                    Working...
                    X