mySQL security

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Paul Furman
    #1

    mySQL security

    How to I clean up SQL hacking such as this:
    /?PAGE=mycode.ph p&filter_in_url =%27+and+%27a%2 7%3D%27b%27+UNI ON+ALL+SELECT+m y_table%2C+my_t able%2C+my_fiel d%2C+<snip>+FRO M+another_table +%23
    I've got 'filter_in_url' that searches & limits the display but got
    hacked with something like the above line. I just want a couple words
    but not huge long malicious type stuff obviously. I will sometimes need
    to have quotes in the filter string (%27) although now that I check this
    doesn't currently work but I guess that's a separate issue. I'm not even
    sure what the %2C or %23 are doing. I suppose I could truncate the
    filter to a couple dozen characters, there probably isn't much that
    could be done in the length that I need.

    My current approach is to str_replace 'JOIN' 'DROP' etc. This is not a
    high security web page, really boring stuff, just some hacker got bored
    & decided to pick on it for fun.

    Thanks
    Tags: None
    • Andy Jeffries
      #2
      Re: mySQL security

      On Wed, 22 Mar 2006 21:52:11 +0000, Paul Furman wrote:
      [color=blue]
      > How to I clean up SQL hacking such as this:
      > /?PAGE=mycode.ph p&filter_in_url =%27+and+%27a%2 7%3D%27b%27+UNI ON+ALL+SELECT+m y_table%2C+my_t able%2C+my_fiel d%2C+<snip>+FRO M+another_table +%23
      > I've got 'filter_in_url' that searches & limits the display but got hacked
      > with something like the above line. I just want a couple words but not
      > huge long malicious type stuff obviously. I will sometimes need to have
      > quotes in the filter string (%27) although now that I check this doesn't
      > currently work but I guess that's a separate issue. I'm not even sure what
      > the %2C or %23 are doing. I suppose I could truncate the filter to a
      > couple dozen characters, there probably isn't much that could be done in
      > the length that I need.
      >
      > My current approach is to str_replace 'JOIN' 'DROP' etc. This is not a
      > high security web page, really boring stuff, just some hacker got bored &
      > decided to pick on it for fun.
      >
      > Thanks[/color]

      I'd go with passing each of your parameter through:

      PHP: mysql_real_escape_string - Manual
      http://uk.php.net/mysql-real-escape-string
      Escapes special characters in a string for use in an SQL statement


      It will escape all quotes properly.

      Cheers,


      Andy


      --
      Andy Jeffries MBCS CITP ZCE | gPHPEdit Lead Developer
      http://www.gphpedit.org | PHP editor for Gnome 2
      http://www.andyjeffries.co.uk | Personal site and photos

        Comment

        • Paul Furman
          #3
          Re: mySQL security

          Andy Jeffries wrote:
          [color=blue]
          > On Wed, 22 Mar 2006 21:52:11 +0000, Paul Furman wrote:
          >
          >[color=green]
          >>How to I clean up SQL hacking such as this:
          >>/?PAGE=mycode.ph p&filter_in_url =%27+and+%27a%2 7%3D%27b%27+UNI ON+ALL+SELECT+m y_table%2C+my_t able%2C+my_fiel d%2C+<snip>+FRO M+another_table +%23
          >>I've got 'filter_in_url' that searches & limits the display but got hacked
          >>with something like the above line. I just want a couple words but not
          >>huge long malicious type stuff obviously. I will sometimes need to have
          >>quotes in the filter string (%27) although now that I check this doesn't
          >>currently work but I guess that's a separate issue. I'm not even sure what
          >>the %2C or %23 are doing. I suppose I could truncate the filter to a
          >>couple dozen characters, there probably isn't much that could be done in
          >>the length that I need.
          >>
          >>My current approach is to str_replace 'JOIN' 'DROP' etc. This is not a
          >>high security web page, really boring stuff, just some hacker got bored &
          >>decided to pick on it for fun.
          >>
          >>Thanks[/color]
          >
          >
          > I'd go with passing each of your parameter through:
          >
          > http://uk.php.net/mysql-real-escape-string
          >
          > It will escape all quotes properly.[/color]

          But if I want to use quotes, that would remove them right? There are
          cases where the user input should have quotes with my application.

            Comment

            • x0054
              #4
              Re: mySQL security

              Paul Furman <paul-@-edgehill.net> wrote in
              news:ublUf.4124 $tN3.3662@newss vr27.news.prodi gy.net:
              [color=blue]
              > Andy Jeffries wrote:
              >[color=green]
              >> On Wed, 22 Mar 2006 21:52:11 +0000, Paul Furman wrote:
              >>
              >>[color=darkred]
              >>>How to I clean up SQL hacking such as this:
              >>>/?PAGE=mycode.ph p&filter_in_url =%27+and+%27a%2 7%3D%27b%27[/color][/color][/color]
              +UNION+ALL+SE[color=blue][color=green][color=darkred]
              >>>LECT+my_tabl e%2C+my_table%2 C+my_field%2C+< snip>+FROM+anot her_table+%[/color][/color][/color]
              23[color=blue][color=green][color=darkred]
              >>>I've got 'filter_in_url' that searches & limits the display but got
              >>>hacked with something like the above line. I just want a couple words
              >>>but not huge long malicious type stuff obviously. I will sometimes
              >>>need to have quotes in the filter string (%27) although now that I
              >>>check this doesn't currently work but I guess that's a separate
              >>>issue. I'm not even sure what the %2C or %23 are doing. I suppose I
              >>>could truncate the filter to a couple dozen characters, there
              >>>probably isn't much that could be done in the length that I need.
              >>>
              >>>My current approach is to str_replace 'JOIN' 'DROP' etc. This is not
              >>>a high security web page, really boring stuff, just some hacker got
              >>>bored & decided to pick on it for fun.
              >>>
              >>>Thanks[/color]
              >>
              >>
              >> I'd go with passing each of your parameter through:
              >>
              >> http://uk.php.net/mysql-real-escape-string
              >>
              >> It will escape all quotes properly.[/color]
              >
              > But if I want to use quotes, that would remove them right? There are
              > cases where the user input should have quotes with my application.
              >[/color]

              No, it will not remove them, it will escape them. That's why it's named
              mysql-real-__escape__-string

              - Bogdan

                Comment

                • Andy Jeffries
                  #5
                  Re: mySQL security

                  On Wed, 22 Mar 2006 23:35:54 +0000, Paul Furman wrote:[color=blue][color=green]
                  >> I'd go with passing each of your parameter through:
                  >>
                  >> http://uk.php.net/mysql-real-escape-string
                  >>
                  >> It will escape all quotes properly.[/color]
                  >
                  > But if I want to use quotes, that would remove them right? There are cases
                  > where the user input should have quotes with my application.[/color]

                  No, not remove - escape.

                  So it would convert:

                  INSERT INTO foo (bar) VALUES ('don't you');

                  (which is broken) in to :

                  INSERT INTO foo (bar) VALUES ('don\'t you');

                  so it inserts in to the database. This would stop your hacking attempts
                  because strings like: something' UNION ALL SELECT...
                  would become:

                  SELECT * FROM foo WHERE bar='something\ ' UNION ALL SELECT...';

                  It's quite safe and makes sure any quotes are inserted in to the column
                  you wrap in that function rather than breaking out in to separate SQL
                  statements or clauses.

                  Cheers,


                  Andy


                  --
                  Andy Jeffries MBCS CITP ZCE | gPHPEdit Lead Developer
                  http://www.gphpedit.org | PHP editor for Gnome 2
                  http://www.andyjeffries.co.uk | Personal site and photos

                    Comment

                    • Paul Furman
                      #6
                      Re: mySQL security

                      Andy Jeffries wrote:
                      [color=blue]
                      > On Wed, 22 Mar 2006 23:35:54 +0000, Paul Furman wrote:
                      >[color=green][color=darkred]
                      >>>I'd go with passing each of your parameter through:
                      >>>
                      >>>http://uk.php.net/mysql-real-escape-string
                      >>>
                      >>>It will escape all quotes properly.[/color]
                      >>
                      >>But if I want to use quotes, that would remove them right? There are cases
                      >>where the user input should have quotes with my application.[/color]
                      >
                      >
                      > No, not remove - escape.
                      >
                      > So it would convert:
                      >
                      > INSERT INTO foo (bar) VALUES ('don't you');
                      >
                      > (which is broken) in to :
                      >
                      > INSERT INTO foo (bar) VALUES ('don\'t you');
                      >
                      > so it inserts in to the database. This would stop your hacking attempts
                      > because strings like: something' UNION ALL SELECT...
                      > would become:
                      >
                      > SELECT * FROM foo WHERE bar='something\ ' UNION ALL SELECT...';
                      >
                      > It's quite safe and makes sure any quotes are inserted in to the column
                      > you wrap in that function rather than breaking out in to separate SQL
                      > statements or clauses.[/color]

                      Thanks for the explanation. If I understand correctly then it would
                      allow me to use quotes when I want to as well as disabling malicious
                      quoting. That's great.

                        Comment

                        • R. Rajesh Jeba Anbiah
                          #7
                          Re: mySQL security

                          Andy Jeffries wrote:[color=blue]
                          > On Wed, 22 Mar 2006 21:52:11 +0000, Paul Furman wrote:
                          >[color=green]
                          > > How to I clean up SQL hacking such as this:
                          > > /?PAGE=mycode.ph p&filter_in_url =%27+and+%27a%2 7%3D%27b%27+UNI ON+ALL+SELECT+m y_table%2C+my_t able%2C+my_fiel d%2C+<snip>+FRO M+another_table +%23[/color][/color]
                          <snip>[color=blue]
                          > I'd go with passing each of your parameter through:
                          >
                          > http://uk.php.net/mysql-real-escape-string[/color]
                          <snip>

                          The latest doctrine is to use prepared statement
                          <http://ilia.ws/archives/103-mysql_real_esca pe_string-versus-Prepared-Statements.html >

                          --
                          <?php echo 'Just another PHP saint'; ?>
                          Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

                            Comment

                            • Andy Jeffries
                              #8
                              Re: mySQL security

                              On Thu, 23 Mar 2006 00:15:02 +0000, Paul Furman wrote:[color=blue][color=green]
                              >> It's quite safe and makes sure any quotes are inserted in to the column
                              >> you wrap in that function rather than breaking out in to separate SQL
                              >> statements or clauses.[/color]
                              >
                              > Thanks for the explanation. If I understand correctly then it would allow
                              > me to use quotes when I want to as well as disabling malicious quoting.
                              > That's great.[/color]

                              You understand correctly.

                              Cheers,


                              Andy

                              --
                              Andy Jeffries MBCS CITP ZCE | gPHPEdit Lead Developer
                              http://www.gphpedit.org | PHP editor for Gnome 2
                              http://www.andyjeffries.co.uk | Personal site and photos

                                Comment

                                Previous template Next
                                Working...