16 {{ upvoteCount | shortNum }}
16 {{ upvoteCount | shortNum }}

SQL Injection?

by forgottenlife
13 replies
How can I block SQL Injection into my MySQL queries?

I am new to this, and was showing a web developer pal, who just blew my mind when he used an injection.

Any help welcomed.

#injection #sql
  • Profile picture of the author ronrule
    Originally Posted by forgottenlife View Post

    How can I block SQL Injection into my MySQL queries?

    I am new to this, and was showing a web developer pal, who just blew my mind when he used an injection.

    Any help welcomed.
    Stripping out apostrophes from strings before allowing them to be used in a SQL statement would be a good start.

    Personally, in my programming days I used stored procedures for everything - put all of the T-SQL code into stored procedures and then deny the web user access to INSERT/UPDATE/DELETE database tables, but GRANT access to execute the stored procedures. That way even if someone was able to hack the site and get the database username/password all they could do is execute the stored procedures.
    Signature

    -
    Ron Rule
    http://ronrule.com

    {{ DiscussionBoard.errors[8703724].message }}
  • Profile picture of the author Andrew H
    Yeah for the sake of developers that come after you please don't use stored procedures.

    SQL injection is basically allowing a person to execute arbitrary code by not sanitizing their input. So, all that needs to be done is to sanitize the input (simple eh?)

    So some of the old dogs, or amateurs, will quickly jump in and go 'just use mysql_real_escape_string' - don't listen to them.

    So I present you the solution: PDO and prepared statements. Check out this tutorial, if you are serious about programming it will likely be the most important 30 minutes you use this year: Why you Should be using PHP’s PDO for Database Access | Nettuts+

    And don't feel bad, when I first found out about SQL injection my mind was blown and i was in your exact same situation.
    Signature
    "You shouldn't come here and set yourself up as the resident wizard of oz."
    {{ DiscussionBoard.errors[8704250].message }}
  • Profile picture of the author reysmendoza
    Banned
    [DELETED]
    {{ DiscussionBoard.errors[8705494].message }}
    • Profile picture of the author BrandByApi
      This is from bobby-tables.com

      "Use parameterized SQL calls.
      That's it.

      Don't try to escape invalid characters.

      Don't try to do it yourself. Learn how to use parameterized statements.
      Always, every single time.
      The strip gets one thing crucially wrong.

      The answer is not to "sanitize your database inputs" yourself. It is prone to error."


      I always use this website because I change languages so often. It is a very good reference.
      {{ DiscussionBoard.errors[8712726].message }}
  • Profile picture of the author brutecky
    mysql_real_escape_string()

    is a good start
    Signature
    Join over 7500 marketers on my Facebook group and also get tons of free tutorials and software to help you with your business.
    {{ DiscussionBoard.errors[8713426].message }}
    • Profile picture of the author wilsonmarcial
      SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.
      This problem has been known for a while and PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function mysql_real_escape_string.
      What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.
      {{ DiscussionBoard.errors[9469049].message }}
      • Profile picture of the author sjeichman
        I'm not sure why Andrew H has issues with stored procedures. Perhaps it's because MySQL is a clunky open source product and doesn't handle them correctly. You get what you pay for!

        Ronrule has the right idea. Stored procedures and functions in a real database allow you to limit the input by limiting the length of input parameters, In the stored procedure, you can validate that the input is what you expect for the particular query and build the Select statement. As Ronrule also said, you grant access to execute the procedure but allow no direct access to the tables themselves.

        Some folks mentioned a function that escapes certain characters. How does the query work if you are querying on name and the name is something like O'Donnell?

        Another advantages to using stored procedures is that the table and column names in your database do not appear in your web code. Some "security by obscurity"! But every little bit helps, If people don't know the table names, SQL injection of a "Delete From" command becomes very difficult.

        I have been DBA-ing, programming in SQL and interfacing a large identity management database with websites for the past 12 years, and with over 2.5 million queries a year into the database, we have not had any SQL injection!

        My database administration, development, anddata security consulting services are available.

        Scott
        Signature

        Scott Eichman
        http://landscapeplanning.org

        {{ DiscussionBoard.errors[9541271].message }}
  • Profile picture of the author NobleSavage
    Read this discussion:

    php - SQL injection that gets around mysql_real_escape_string() - Stack Overflow
    {{ DiscussionBoard.errors[9541363].message }}
  • Profile picture of the author MRgauss
    which programming language do you use?
    Signature
    Get FREE Videos to your Youtube Channel On autopilot!
    YTwizard
    {{ DiscussionBoard.errors[9546197].message }}
    • Profile picture of the author NobleSavage
      Originally Posted by MRgauss View Post

      which programming language do you use?
      Python whenever possible. I tend to get drug into PHP.
      {{ DiscussionBoard.errors[9548780].message }}
  • Profile picture of the author mralexanderca
    Are you using any platform such as WordPress or any framework for coding? Or is is raw php code.

    Essentially all it is: You have to ensure any values that are passed to mysql from user input are sanitized. It varies slightly from query to query but the idea is the same.

    I am assuming your application is rather small so if you would like to provide some references I am sure we could help you out.

    Mr Alexander
    {{ DiscussionBoard.errors[9548160].message }}
  • Profile picture of the author dad2four
    PDO or MySQLi

    Good luck if you are using codeigniter since MySQLi is not implemented.....
    Signature
    {{ DiscussionBoard.errors[9553795].message }}
  • Profile picture of the author dad2four
    Wow this post was from November of last year? Who dug this thing up anyway? hehe
    Signature
    {{ DiscussionBoard.errors[9553799].message }}
  • Profile picture of the author Member8200
    //unsafe
    $users = $_POST["users_input"];



    //Safe way to do it.
    $users = mysql_real_escape_string($_POST["users_input"]);
    {{ DiscussionBoard.errors[9561221].message }}

Trending Topics