SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 44

Hybrid View

  1. #1
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Secure your SQL queries!!!

    I thought I'd better make sure that everyone is aware of a particular class of security vulnerabilities called malicious SQL code injections. While there are many other security vulnerabilities, these are particularly dangerous - left unchecked, a malicious user could access or otherwise manipulate any data in the SQL Server, as well as execute DOS commands, etc.

    These vulnerabilities are nothing new, but security is usually the least of most developers' worries, and some developers may not even have considered this angle.

    If you are not already familiar with this topic, it is imperative that you read up pronto - you could be unwittingly putting all of your live DB data at risk, and possibly the server itself too!

    Please note that although most exploits using this technique target MS SQL Server, some can be applied to Access (and other DBs) too, so you're not necessarily invincible just because you've got a small site running off Access....!

    Here's a few sites to start you off (!):
    http://www.google.com/search?q=SQL+injection

    Hot off the presses - four rules for SQL Injection protection:
    http://msdn.microsoft.com/en-us/libr...SQL.80%29.aspx



    M@rco
    Last edited by TechnoBear; Jan 14, 2013 at 06:32. Reason: Updated MS link

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's a good whitepaper (maybe we subscribe to the same newsletters? ):
    http://www.spidynamics.com/whitepape...LInjection.pdf

  3. #3
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i've looked through a couple of the articles (not the whitepapers yet) and wondered if this can also be passed through a querystring in the url? would that mean that we should validate any querystring variables? that is something i've never thought about before - thanks for the heads up guys!

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,283
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by bbolte
    i've looked through a couple of the articles (not the whitepapers yet) and wondered if this can also be passed through a querystring in the url? would that mean that we should validate any querystring variables? that is something i've never thought about before - thanks for the heads up guys!
    Absolutely, you should be validating your querystrings at all times! Always ensure that they are numeric if you're expecting numerics, etc.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    in terms of security (as the articles discuss), what should i be validating against? i understand checking for datatypes and single apostrphies, etc. but what else? whether in querystrings or form fields, what should i be doing?

  6. #6
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mainly I started useing stored procedures and the ADODB.Command object with explicitly defined parameters. This garuntees that any SQL code a hax0r injects to your application will simply be added as a parameter value and will either error out due to a type mismatch or will be stored as string data. This is a very good defense (in my opinion anyways) against SQL injection.

    I also consider all Querystring/Form/Cookie items to be subject to tampering. This means they get checked for datatype and sometimes range. If one of the values fails the test then you should either show them an error message or, depending on the nature of the app, sometimes you can just "fix" the data and move along.
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  7. #7
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does ADO sanatize (e.g. escape single quotes) in parameters? If so, then I would say that is all you need to do. If you can pass in single quotes and break the SQL then there's a significant hole right there!

    Same thing with integer values -- can you pass in an integer like this:

    45, dfklsjf

    Or will it error?

  8. #8
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alas, ADO does not sanitize data - problems caused by strings containing unescaped quotes are posted on a regular basis here. Likewise with your integer example - it's the same problem as trying to do this in an ASP script:
    Code:
    <%
    i = CInt("45, dfklsjf")
    %>
    So, we the developers are responsible for validating and sanitizing (where possible) all user-entered data in order to ensure that such problems do not occur during the course of normal data entry, and especially to ensure that inherent security vulnerabilities are shielded from malicious users.


    M@rco

  9. #9
    SitePoint Addict EvilDoppler's Avatar
    Join Date
    Dec 2001
    Location
    Perth, Australia
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    I've always made it a habit to validate my input before subjecting it to the database..was taught a long long time ago to be careful about that but still shocked me to see some of my code being uhmmm...lets just say..less than secure...one problem was integers added to text fields (well i wanted only numbers but the text field could of course contain anything)..

    simple php validation (which i'm not sure was in the article or not) is is_int()..

    i've become slack...

    anyways, the article (downloadable pdf whitepaper) kinda perked me back up on my toes again..

    nice article!!
    * Due to the last tax raise our prices has risen
    * - answers w/o thought $1 - answers w/ thought $3
    * - correct answers $10
    * - Dumb stares are still free

  10. #10
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Although it doesn't excuse you NOT using data validation before inserting it into the DB, using the ADODB.RecordSet .Update and .AddNew methods in conjunction with their optional parameters (an array of field names and an array of field values) can make your scripts a lot more impervious, since the data being inserted is never interpreted by the DB, and so defeats the particular type of SQL injection vulnerability that we have been discussing.

    Code:
    FieldsList = "ID|Name|Email"
    ValuesList = "3|M@rco|marcojt@antisocial.com"
    
    RS.AddNew split(FieldsList,"|"),split(ValuesList,"|")
    (Of course, the arrays can be generated in any way you want, not just the way I have above)

    This has been my preferred method of inserting/updating data for ages - I rarely need to use INSERT or UPDATE statments at all!


    M@rco

  11. #11
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by M@rco
    Alas, ADO does not sanitize data - problems caused by strings containing unescaped quotes are posted on a regular basis here.
    ADO will deal with apostrophes if you use a Command object, with Parameters, even if you use an sql string or stored procedure.

    <snip/>

    Using Parameters has also got the advantage that you can specify the data type and size. If you use SP's as well then it's easier to configure the security access to the DB.

    You'd still have to validate any data before though.
    Last edited by Mittineague; Dec 18, 2010 at 12:57. Reason: pre-new-sticky cleanup

  12. #12
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I suppose the biggest thing at the mo for all PHP programmers, something that the majority of scripts round SPF currently don't do, is learning to program with register_globals off. That is surely the easiest way to use malicious query strings that attempt to overcome most attempted securities.

    Rather than using something like

    PHP Code:
    if(isset...) // or
    if ($variable=="value"
    use the built in arrays like _POST etc etc.
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  13. #13
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Compulsory reading...!

    This is also excellent reading material on the subject (although presented from an ASP point of view, 99% of it applies to every server-side scripting language out there):
    http://www.adopenstatic.com/resource...Validation.asp


    M@rco

  14. #14
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay, so to pick ya'lls brains a little....how do you validate a text field? If there is no limits on a text field, how do you validate it? I'm using MySQL but this pertains to any db. I also use PHP. In MySQL, varchar can only have 255 characters....so anything more than that, say...an article, requires a text field. How do you make sure no malicious code is being passed there?

    Sketch
    Aaron Brazell
    Technosailor



  15. #15
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm confused -- you do the same thing for all character fields, e.g.

    PHP Code:
    mysql_query"INSERT INTO mytable
                      VALUES( 
    $someint
              " 
    addslashes$somecharacter ) . ",
              " 
    addslashes$lotsoftext    ) . " )" ); 

  16. #16
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Last edited by M@rco; Jun 19, 2002 at 10:33.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  17. #17
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  18. #18
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got to check that article too but it was for asp and ms sql.

    Thanks for the other two articles. Still i'm not very sure of how to prevent these attacks.

    I think you were discussing something about validating every variable that comes from the url. For instance if i have the following url:

    http://www.mysite.com/update.php?id=12&name=toly

    Lets just say i want to update a record on my database with the id equal to 12 and name equal to toly. If i make my script to check that the variable ID only contain numbers and the variable name only contain letters, would that be enough?
    Last edited by Mittineague; Dec 18, 2010 at 13:13. Reason: pre-new-sticky cleanup

  19. #19
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Toly for the number value yes that is acceptable.

    However for the character string you must make sure to escape end string characters so commands will not be processed.

    For example, in virtually every RDBMS you use single quotes around strings. Let's say I was logging in to this forum system and internally the SQL is like this:
    Code:
    SELECT userid
      FROM user
     WHERE username = '$username'
       AND password = '$password'
    Typically the application code then says if the userid exists, then we have a match, so go ahead and log them in.

    However, if I entered for my password:
    ' or 1=1 or password = '

    Then the SQL would look like this:

    Code:
    SELECT userid
      FROM user
     WHERE username = 'MattR'
       AND password = '' or 1=1 or password = ''
    Since 1 is equal to 1, it will think I matched and log me in. If you escape the string (T-SQL style in this example), it will look like this:

    Code:
    SELECT userid
      FROM user
     WHERE username = 'MattR'
       AND password = ''' or 1=1 or password = '''
    Which will check your password being the entire string, and will not match.

  20. #20
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks for the reply Matt.

    So what you are trying to say is that i should use two single quoates rather than one to avoid commands to execute?

    For instace:

    PHP Code:
    SELECT FROM users
       WHERE username 
    '$username'
       
    AND password ''$password'' 
    I don't know if this is what you meant. I have a login section on my site and i tried to put the thing you wrote: ' or 1=1 or password = ' in the password field but it didn't give me access.

    Thanks again.

  21. #21
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Toly
    So what you are trying to say is that i should use two single quoates rather than one to avoid commands to execute?
    no, what he is saying is to feed all the data you're gathering from the form through some kind of procedure that will replace all single quotes with double single quotes. here is a simple one for ASP/VBScript (i'm sure you have something equivalent in PHP):
    Code:
    Function fixQuotes(theString)
    	fixQuotes = Replace(theString, "'", "''")
    End Function

  22. #22
    SitePoint Zealot
    Join Date
    Jul 2002
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "default deny regular experssion" from the SPI dynamics solutions section.

    Does that mean to select a set amount of characters so your 'user input' (whatever it might be) will be removed of any qutoes or other symbols that you dont want?

    I was wondering if this solution will work. (I am sure someone has thought of it already and it might sound really dumb since I am a newbie) Let's say you have an admin page for your webmaster to log into to perform multiple sql tasks. Of course, general users and the public doesnt need to enter the page. Therefore, instead of just using sql injection prevention methods, cant you limit access to certain IP addresses. I am aware some ISP give dynamic IPs, but for some ADSL/DSL companies you get a static ip. I read some where you can log the users ip using php. All that has to be done is to store the static ip (the computer that the admin uses) somewhere safe. Everytime someone tries to go to the admin page, their ip will be matched against the stored one. If its different, automatic redirect so the no input can even take place.

    I dont know if that works, I just thought of it out of the blue in light of reading this article. Don't laugh if its super stupid, I am new to coding security and all. Tell me if it works.

    Spaz
    Just a little boy trying to make his way into the world...
    ~~~Spaz Boy
    Programmer + Gamer = ProGamer

  23. #23
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's not a stupid suggestion at all - it is indeed possible to restrict access to something by (virtually) any criteria, and a system such as that which you suggest could indeed be implemented if desired.

    However, in practice, I don't think that it would be a good idea at all, since one of the best features of any remote admin system is the ability to log on from anywhere and fix/alter something, but if you've restricted admin access to only a single IP, then you're screwed if you're located anywhere else when you need to make the change!!

    Hence locking down admin access to only a single PC or IP address is not a good idea for that reason alone (and I'm sure that there are plenty of others).
    Last edited by M@rco; Aug 15, 2002 at 13:37.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  24. #24
    SitePoint Zealot
    Join Date
    Jul 2002
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    True... It will be a big problem if the admin doesnt have a static ip. Come to think of it, most of the time admins might need to change stuff when away from the office.

    Another question:
    After reading some of the articles in this post, it makes me think hard to prevent sql injection attacks.

    If I am remember correctly, the "Dynamic something (I forgot the second part of the company name. I just know its one of the first articles posted)" said its simply not enough by removing single quotes, backslashes and all. So what else can we do?

    By removing all the possible symbols needed for "fooling" the code, how can the attacks take place?

    There are many suggestions to actually limit or set a range of characters or values to be used. Something like (according to my memory):
    [a-z], [A-Z], [0-9]
    Won't that be limiting if a user requires " ' " in their search?

    Spaz
    Just a little boy trying to make his way into the world...
    ~~~Spaz Boy
    Programmer + Gamer = ProGamer

  25. #25
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think you would have to evaluate what data you are getting. if it is only supposed to be numeric, strip everything except numerals (use regular expressions). <snip/>
    Last edited by Mittineague; Dec 18, 2010 at 13:20. Reason: pre-new-sticky cleanup


Bookmarks

Posting Permissions

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