13 Replies Latest reply on Jun 11, 2007 6:53 AM by Newsgroup_User

    Best Database Communication Xtra/Method ??

    AntiTRUST� Level 1
      Hello everybody!
      I need to know what you guys consider the best Xtra or the best Method to connect to a database for Adding, Editing or Deleting records over Internet ??

      I need to start this thing asap, therefore i need some expert's advices. Any suggestions or comments are mostly welcome.


      Regards,
      Aamir
        • 1. Re: Best Database Communication Xtra/Method ??
          James Newton, ACP Level 3
          I've been very satisfied with Tabuleiro's Nebulae Multiuser Server which you can install on the same server as the database. You can then use the built-in Multiuser Xtra to communicate with the database remotely.
          • 2. Re: Best Database Communication Xtra/Method ??
            Applied CD Level 1
            You can find an exhaustive explanation of how to connect to an internet database without any xtras here:

            http://www.shocknet.org.uk/index.asp

            It takes a long time to go through that material, I distilled it down to one tiny ASP script that glues my Director code to a remote SQL database and I’m very happy with it. Essentially, once I got it working I can treat the ASP code as invisible. I build the SQL query as a sting in Director, send it to the script and wait for the result which comes back in the form of a list. There is almost no error checking in the code so to use it you must be fairly comfortable with SQL and list processing. Someday I hope to improve the ASP code and build a standard set of Director behaviors that I can call to interact with the db, but this is what I’ve got now:

            The ASP code is server side, duh ;-) and called dbInterface.asp The trickiest thing about using it is getting the connection string right (strCon) … This string must point to your data source and call the proper driver (in my example, Access 2000)

            The lingo code is an example of a user log-in script. It looks in the db for a user ID and makes sure the encrypted passwords match. To save space I haven’t included all of the utility subroutines, just the ones important to connecting to the db and processing the result. If you need to know what any of the other code is I'll be happy to post up.
            • 3. Re: Best Database Communication Xtra/Method ??
              LOOPING_Richard
              quote:

              Originally posted by: Applied CD
              You can find an exhaustive explanation of how to connect to an internet database without any xtras here:

              http://www.shocknet.org.uk/index.asp

              It takes a long time to go through that material, I distilled it down to one tiny ASP script that glues my Director code to a remote SQL database and I’m very happy with it. Essentially, once I got it working I can treat the ASP code as invisible. I build the SQL query as a sting in Director, send it to the script and wait for the result which comes back in the form of a list. There is almost no error checking in the code so to use it you must be fairly comfortable with SQL and list processing. Someday I hope to improve the ASP code and build a standard set of Director behaviors that I can call to interact with the db, but this is what I’ve got now:

              The ASP code is server side, duh ;-) and called dbInterface.asp The trickiest thing about using it is getting the connection string right (strCon) … This string must point to your data source and call the proper driver (in my example, Access 2000)

              The lingo code is an example of a user log-in script. It looks in the db for a user ID and makes sure the encrypted passwords match. To save space I haven’t included all of the utility subroutines, just the ones important to connecting to the db and processing the result. If you need to know what any of the other code is I'll be happy to post up.




              Hello Applied CD!
              Your posting of the ASP script is interesting, but I really do hope this is not all of your script!

              There is no error checking anywhere, no encryption, no password protection so the script is open to the world...
              What is even worse is that you pass the input to the script straight to the database engine!
              This is a MAJOR security risk.

              It is very easy to find the URL to the script, and then erasing all your data even without knowing any passwords.

              As I said I hope this is not all of it, but if it is....
              PLEASE put some security measures in before this gets all around the community.

              Kind regards,
              LOOPING Multimedia,
              Richard.
              • 4. Re: Best Database Communication Xtra/Method ??
                AntiTRUST� Level 1
                Thank you guys for your reviews and suggestions, i really appreciate this and hope for more suggestions and more comments, as the more we dig, the more we get.

                I am going to check both ways.

                Thanks,
                Aamir
                • 5. Re: Best Database Communication Xtra/Method ??
                  Applied CD Level 1
                  quote:

                  There is no error checking anywhere, no encryption, no password protection so the script is open to the world...


                  Yeah, I’m afraid that’s all there is for now. I wrote the script as a side project/online game for a few close friends (and as a proof of concept) so the need for security was minimal. Looking back I’ll probably add an authentication variable to prevent the script from responding to anything other than my shockwave app (at least blocking casual snoopers from hijacking the script). That having been said, I’ve seen a number of ASP scripts deployed in real world applications that are no more secure than this one but of course that doesn’t make it right. If anyone has suggestions on how to make it more secure I’d be happy to modify it.

                  As for error checking, it’s all on the lingo side. I did this for two reasons, the first is that I’ve been coding lingo for 10 years but I’m an ASP noob, the second, I wanted the ASP script to be completely “data neutral” and as transparent as possible. I looked at a number of examples while putting this together and I noticed that data processing was inconsistently divided between ASP and lingo. I’m not sure what best practices are here but I did notice that by moving all of the processing, error checking, etc… to lingo I streamlined the development enormously.

                  BTW: the passwords in the db are encrypted so if someone manages to dump the contents of the db at least my friend’s passwords are safe.
                  • 6. Re: Best Database Communication Xtra/Method ??
                    AntiTRUST� Level 1
                    Well guys, i need to know one thing, i am a bit confused here. I.e. how can we read the server side script? Like if i upload an asp script on to the server, how can you download that file?

                    Because if we just put the link of an ASP file on the browser OR if we try to download through some download manager, in this case for example:
                    http://www.somewebsite.com/login.asp
                    We get errors or some Unauthorized message, etc...

                    As my knowledge in this is quite limited, i really would like to know how is this possible, as Richard is stating.

                    Thanks,
                    Aamir
                    • 7. Re: Best Database Communication Xtra/Method ??
                      Level 7

                      "Applied CD" <webforumsuser@macromedia.com> wrote in message
                      news:f4bst7$4mh$1@forums.macromedia.com...
                      >
                      quote:

                      There is no error checking anywhere, no encryption, no password
                      > protection
                      > so the script is open to the world...

                      >
                      > Yeah, I?m afraid that?s all there is for now. I wrote the script as a side
                      > project/online game for a few close friends (and as a proof of concept) so
                      > the
                      >


                      > BTW: the passwords in the db are encrypted so if someone manages to dump
                      > the
                      > contents of the db at least my friend?s passwords are safe.

                      Hello again...

                      No, you do not understand, it is really really dangerous.

                      I have just changed the password of the account name stormbringergrey to " "
                      (a space)

                      [["7","stormbringergrey","he"],[""]]

                      Go check for yourself. This took me 1 hour.
                      I will mail you the procedure if you give me your email.
                      Please. (AGAIN) do NOT use this script unmodified on the web!!

                      Richard.



                      • 8. Best Database Communication Xtra/Method ??
                        Applied CD Level 1
                        Aamir points out the big advantage you have over a random hacker, by publishing the connection string half your work is done. I’ve attached a revised version of the script that is a little more secure by adding a password sent from shockwave that must be matched in the script before it will do anything, of course if you publish the script with the password in it we’re right back to where we started but at least it’s a start.

                        I don’t have time just at the moment to look into other options for securing the datasource (paying projects go to the front of the line ;-) … but I’m not exactly sure why this approach is any less secure than your typical ASP site which also communicates directly with the datasource. I operate a whitewater kayaking site using snitz forums ( http://forum.snitz.com/) and I’ve looked through the db connection code, I don’t see any special form of protection there either.

                        Anyway, as I’ve said before, I’m open to suggestions to improve the security of the script. If you need something very secure I would suggest looking at one of the commercial connection xtras however this works, it’s free, and it doesn’t require software installed server side so if we can make it a little more secure I think it has potential.

                        - Bob


                        PS: I suppose you could encrypt the SQL string sent from shockwave and have the ASP script decrypt the string with a matching key but I don’t see why that would be any better than what is below. Even with encrypt/decrypt if you figure out a way to download the script you’ll have access to the encryption algorithm and the key, it then wouldn’t be too hard to write a script that spoofed the original shockwave app.
                        • 9. Re: Best Database Communication Xtra/Method ??
                          Level 7
                          Hello,

                          The problem is not in the connection string at all!

                          The problem is here:
                          > strSQL = request("strSQL")
                          That makes this work from a browsers' address bar:

                          http://dbInterface.asp?mode=write&strSQL=DELETE * FROM memberID

                          That should NEVER EVER be possible from the web. :-(

                          Dont get me wrong.... I have made this exact mistake myself before.
                          I was just lucky I got away with it...
                          (Sorry to have messed up your database, but I just wanted to show how
                          incredibly insecure this script is. And I would not have done it if you had
                          more than one user in the table...)

                          You have tried to implement all security in the Lingo part, but that is only
                          the front door.
                          The back-door (the webURL as above) is wide open, and not secured at all.

                          What you need is database security, so online users can not in any way
                          change other users' data, or perform administrative tasks. If I was able to
                          delete only my own account then that would be sort of OK.
                          As far as I know you can not do that in Access, so the asp should implement
                          that.

                          The password addition does not make much of a difference, because that
                          password is the same for all users, and is always the same anyway. Once you
                          have succesfully logged in to the game you again have full access to the
                          database, because the password to the script is in the URL....
                          Then the same as above will again work.

                          Its not easy to get this secure, and I do not claim I have the solution.
                          I can check this weekend if I can find something better, on the web, or else
                          see if I can put something together.

                          Take care,
                          Richard.




                          "Applied CD" <webforumsuser@macromedia.com> schreef in bericht
                          news:f4cbd7$mud$1@forums.macromedia.com...
                          > Aamir points out the big advantage you have over a random hacker, by
                          > publishing
                          > the connection string half your work is done. I?ve attached a revised
                          > version
                          > of the script that is a little more secure by adding a password sent from
                          > shockwave that must be matched in the script before it will do anything,
                          > of
                          > course if you publish the script with the password in it we?re right back
                          > to
                          > where we started but at least it?s a start.
                          >
                          > I don?t have time just at the moment to look into other options for
                          > securing
                          > the datasource (paying projects go to the front of the line ;-) ? but I?m
                          > not
                          > exactly sure why this approach is any less secure than your typical ASP
                          > site
                          > which also communicates directly with the datasource. I operate a
                          > whitewater
                          > kayaking site using snitz forums ( http://forum.snitz.com/) and I?ve looked
                          > through the db connection code, I don?t see any special form of protection
                          > there either.
                          >
                          > Anyway, as I?ve said before, I?m open to suggestions to improve the
                          > security
                          > of the script. If you need something very secure I would suggest looking
                          > at one
                          > of the commercial connection xtras however this works, it?s free, and it
                          > doesn?t require software installed server side so if we can make it a
                          > little
                          > more secure I think it has potential.
                          >
                          > <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
                          > <%
                          > dim oConn, oRS, strCon, strSQL, queryMode, conPasWrd
                          >
                          > conPasWrd = request("conPasWrd")
                          > if conPasWrd = ?some made up password passed from shockwave? then
                          > queryMode = request("mode")
                          > strSQL = request("strSQL")
                          >
                          > strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
                          > server.mapPath("characterSheets.mdb") & ";"
                          > set oConn = server.createObject("ADODB.Connection")
                          > oConn.open strCon
                          >
                          > if queryMode = "write" then
                          > oConn.Execute(strSQL)
                          > response.write "done"
                          > else
                          > set oRS = server.createObject("ADODB.Recordset")
                          > oRS.open strSQL, oConn
                          > if not oRS.EOF Then
                          > response.write "[[" & CHR(34)
                          > response.write oRS.getString(,,CHR(34) & "," & CHR(34),CHR(34) & "],[" &
                          > CHR(34),"]")
                          > response.write CHR(34) & "]]"
                          > else
                          > response.write "no records found"
                          > end if
                          > oRS.Close
                          > set oRS = Nothing
                          > end If
                          >
                          > oConn.Close
                          > set oConn = Nothing
                          > end if
                          > %>
                          >


                          • 10. Re: Best Database Communication Xtra/Method ??
                            BG Solutions
                            No problem with the database, I could fix it but it’s only a test platform and out of date anyway.

                            I wouldn’t discount the password addition. The password isn’t authenticating user access, it’s telling the ASP script that the incoming SQL string was generated by my shockwave app and not a rogue string from someone’s browser or a predator script. I use postNetText to pass my strings so the user will never see the hardcoded password. Users still need to log in with their own user ID and password. As for user access to the database, they never have direct interaction. My app provides the interface to the database and controls what they can do so even if they pass authentication they would not be able to issue a DELETE command. I suppose a hacker might try embedding SQL code in a text input field which would more than likely just result in a SQL syntax error but it could be easily prevented anyway by checking the outgoing strings for SQL commands.
                            • 11. Re: Best Database Communication Xtra/Method ??
                              Level 7
                              Hello,
                              you should definately read up on web security!

                              > No problem with the database, I could fix it but it?s only a test platform
                              > and
                              > out of date anyway.
                              > I wouldn?t discount the password addition. The password isn?t
                              > authenticating
                              > user access, it?s telling the ASP script that the incoming SQL string was
                              > generated by my shockwave app and not a rogue string from someone?s
                              > browser or

                              No, not true. The script has actually no clue as to where the request is
                              coming from.
                              As long as the password is correct the script doesnt see the difference.

                              > a predator script. I use postNetText to pass my strings so the user will
                              > never
                              > see the hardcoded password. Users still need to log in with their own user
                              > ID

                              Getting at the POST info is easy:
                              http://www.programurl.com/software/http-request.htm

                              And besides.. you send them as POST, but your script doesnt care:
                              strSQL = request("strSQL")

                              This GET request works just as well:
                              http://www..com/xxx/yyy/dbinterface.asp?mode=write&strSQL=DELETE+*+FROM+memberID

                              And even if it did check for POST, I can simply create a webform with
                              action="POST" and off we go again...

                              What happens if I replace the sql in the URL above with:
                              strSQL=UPDATE+memberID+SET+password+=+'he'+WHERE+username+LIKE+'stormbringergrey'

                              > and password. As for user access to the database, they never have direct
                              > interaction. My app provides the interface to the database and controls
                              > what

                              No, that is not correct. Your ASP script provides the interface to the
                              database.
                              I deleted all your records without even seeing your shockwave interface.
                              Really!
                              Check your IIS log, its all in there.

                              > they can do so even if they pass authentication they would not be able to
                              > issue
                              > a DELETE command. I suppose a hacker might try embedding SQL code in a
                              > text
                              > input field which would more than likely just result in a SQL syntax error
                              > but
                              > it could be easily prevented anyway by checking the outgoing strings for
                              > SQL
                              > commands.
                              >

                              No, not true.
                              How about:
                              http://www..com/xxx/yyy/dbinterface.asp?conPasWrd=some+made+up+password+passed+from+shockw ave&mode=write&strSQL=DELETE+*+FROM+memberID


                              Your mistake is thinking that because you provide a user interface, your
                              users will actually use it. They might not. Many just poke around for fun..
                              A hacker will not even go trough your shockwave interface and go straight to
                              the asp, just like I showed you above. You have a false sense of security by
                              relying on shockwave for checks, because the security hole is on the
                              SERVER side, not on the client side.

                              There is lots of info on securing mail forms on the web, preventing them
                              from being abused by spammers. That is a good starting point for
                              re-designing the server script.

                              http://www.w3schools.com/php/php_secure_mail.asp

                              There is also the danger of sql-injection, check this:
                              http://www.unixwiz.net/techtips/sql-injection.html

                              The bottom line is that anything your asp script takes as input should be
                              thoroughly checked for unwanted or unintended side-effects.
                              Checking in Lingo is just not enough.

                              Regards,
                              Richard.


                              • 12. Best Database Communication Xtra/Method ??
                                BG Solutions Level 1
                                OK, I’ll concede that snooping for the post data presents a problem. From what I gather there are two ways to attack the datasource, 1. by communicating directly with the ASP script bypassing the shockwave interface or 2. by using the shockwave interface and embedding SQL code in a text input field that gets passed as part of the SQL string (SQL injection)

                                Let me suggest the following solutions, with some luck I might have time to implement tonight:

                                1. Use RC4 encryption on the SQL string. Examples of RC4 encryption code in ASP are readily available on the web ( http://www.4guysfromrolla.com/webtech/010100-1.shtml), translating to lingo shouldn’t be hard if it hasn’t been done already. The shockwave app would encrypt the SQL string using an embedded key, the ASP script would decrypt the SQL string using it’s own embedded key. The key is never transmitted so even if you snoop the post data and know the algorithm being used you still don’t have the key. Sending a clear text SQL string to the script won’t work because the decryption process will translate the string into garbage, and as I mentioned above, even if you use your own RC4 encryption you’re still left to guess for the key.

                                2. To prevent SQL injection you need to sanitize the user input. This is fairly straight forward but your SQL injection link contains some practical advice at the end of the article. A simple lingo script to filter user input would be all that is needed.

                                BTW: You might notice that I’m trying to avoid server side filtering of the SQL string. Filtering the string server side would be a great idea except distinguishing “good” SQL from “bad” SQL while still keeping the script open enough to be functional could be kind of tough. For example, on a quick reading I get the impression that “*” and “LIKE” are common ways for hackers to probe the internal structure of an unknown database … users probably wouldn’t miss the asterisk much although when coding all of your queries would have to be explicit (which could quickly become a hassle), however, a user typing “I like goldfish” would be problem.
                                • 13. Re: Best Database Communication Xtra/Method ??
                                  Level 7
                                  BG Solutions wrote:
                                  > OK, I?ll concede that snooping for the post data presents a problem.
                                  > From what I gather there are two ways to attack the datasource, 1. by
                                  > communicating directly with the ASP script bypassing the shockwave
                                  > interface or 2. by using the shockwave interface and embedding SQL
                                  > code in a text input field that gets passed as part of the SQL string
                                  > (SQL injection)

                                  1. Do not pass SQL from the client. Always generate the SQL server-side.
                                  Even better is if you use (e.g.) MS SQL Server with stored procedures so
                                  that the database data doesn't need to be exposed to the ASP_machinename
                                  user.

                                  In conjunction with...

                                  2. Use parameterized queries so that you don't have to worry about SQL
                                  injection attacks.

                                  HTH

                                  Andrew