Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

My Concern for SQL Injection

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    My Concern for SQL Injection

    I have grave concerns about SQL Injection in my A5 app that I am starting to build.

    I've seen reference here (this forum) about using arguments to mitigate this potential problem.

    In neither case did the responders leave enough info for me to figure out what they were really
    talking about. I'm new to A5 but I am a long time programmer.

    If someone can give me a more verbose reply as to how and why this will help, it would be
    appreciated. At this point, I don't really know what you are talking about and my searches
    don't help either. Some examples would be great!!!

    #2
    Re: My Concern for SQL Injection

    This is a general article on the topic - not specifically Alpha Five.

    https://www.owasp.org/index.php/SQL_...on_Cheat_Sheet

    Comment


      #3
      Re: My Concern for SQL Injection

      Here is a long example with arguments; this one is from a grid in the CanInsertRecords event. All of the var with ":" are the arguments, added using args.add.

      FUNCTION CanInsertRecord as v (DataSubmitted as P, Args as p, PageVariables as p, Result as p)
      WITH PageVariables

      '==DETERMINE BROKERAGE COMPANY (TRINAV, ATHEARN)
      vbrokeragecompany = DataSubmitted.fd_BrokerageCompanyName

      '== METHOD
      '== Post Vessel and Client, get their new value (old or new entries)
      '== Get the new Listing Value
      '== Post the new Listing

      dim vVesselid as n = 0
      dim vClientid as n = 0

      '==FIELD VALIDATIONS
      dim ferror as l =.f.
      IF DataSubmitted.fd_ListingLocation = ""
      ferror = .f.
      END IF
      IF DataSubmitted.fd_BrokerName =""
      ferror=.t.
      END IF
      IF ferror = .t.
      Result.Cancel = .t.
      Result.ErrorHTML = "Required fields are blank."
      END IF

      '==VARS
      vesselchoice = DataSubmitted.vesselchoice
      vVesselid = DataSubmitted.fd_VesselID
      vClientid = DataSubmitted.fd_ClientID

      '==NOTE TRINAV/ATHEARNS DIFFERENCE. MAKE CASE STATEMENT IF ADDING THIRD COMPANY
      if vbrokeragecompany = "TriNav"
      fdlocn = DataSubmitted.fd_ListingLocation
      else
      select
      case DataSubmitted.fd_Category = "Vessel"
      fdlocn = DataSubmitted.athearn_rigging
      case DataSubmitted.fd_Category = "Permit"
      fdlocn = DataSubmitted.permitcategory
      case DataSubmitted.fd_Category = "Permit Lease"
      fdlocn = "PL"
      case else
      Result.Cancel = .t.
      Result.ErrorHTML = "Invalid Category for Athearns."
      end select

      end if
      clientchoice = DataSubmitted.clientchoice
      new_VesselName = DataSubmitted.new_VesselName
      '== CONNECTIONS
      dim cn as SQL::Connection
      dim rs as SQL::ResultSet
      dim args as SQL::Arguments
      cn.open("::name::conn")

      cn.PortableSQLEnabled = .t.

      '==VESSEL INSERT

      IF vesselchoice = "New"

      vSelect = <<%txt%
      INSERT INTO tblVessel
      (
      VesselName,
      YearBuilt,
      HullMaterial,
      VesselType,
      Builder,
      LengthFeet,
      LengthInches,
      BreadthFeet,
      BreadthInches
      )
      VALUES
      (
      :new_VesselName,
      :new_YearBuilt,
      :new_HullMaterial,
      :new_VesselType,
      :new_Builder,
      :new_LengthFeet,
      :new_LengthInches,
      :new_BreadthFeet,
      :new_BreadthInches
      )
      %txt%
      args.add("new_VesselName",new_VesselName)
      args.add("new_YearBuilt",val(DataSubmitted.new_YearBuilt))
      args.add("new_HullMaterial",DataSubmitted.new_HullMaterial)
      args.add("new_VesselType",crlf_to_comma(DataSubmitted.new_VesselType.dump()))
      args.add("new_Builder",DataSubmitted.new_Builder)
      args.add("new_LengthFeet",val(DataSubmitted.new_LengthFeet))
      args.add("new_LengthInches",val(DataSubmitted.new_LengthInches))
      args.add("new_BreadthFeet",val(DataSubmitted.new_BreadthFeet))
      args.add("new_BreadthInches",val(DataSubmitted.new_BreadthInches))

      vSelect = evaluate_string(vSelect)
      IF cn.execute(vSelect,args) = .f.
      vErrorMsg = cn.callresult.text + crlf() + vSelect
      ' save_to_file(vErrorMsg,"c:\t1.txt")
      'ErrorOut() ' prints error halt progress if necessary
      END IF
      vVesselid = cn.LastInsertedIdentity()

      END IF

      '==CLIENT INSERT

      IF clientchoice = "New"

      vSelect = <<%txt%
      INSERT INTO tblclients
      (
      Company,
      FirstName,
      LastName,
      Salutation,
      Contact,
      Address,
      Town,
      Province,
      PostalCode,
      Country,
      HomePhone,
      WorkPhone,
      CellPhone,
      SatPhone,
      VesselPhone,
      FaxNumber,
      OtherPhone,
      EmailAddress1,
      EmailAddress2,
      Notes
      )
      VALUES
      (
      :newc_Company,
      :newc_FirstName,
      :newc_LastName,
      :newc_Salutation,
      :newc_Contact,
      :newc_Address,
      :newc_Town,
      :newc_Province,
      :newc_PostalCode,
      :newc_Country,
      :newc_HomePhone,
      :newc_WorkPhone,
      :newc_CellPhone,
      :newc_SatPhone,
      :newc_VesselPhone,
      :newc_FaxNumber,
      :newc_OtherPhone,
      :newc_EmailAddress1,
      :newc_EmailAddress2,
      :newc_Notes
      )
      %txt%

      args.add("newc_Company", DataSubmitted.newc_Company)
      args.add("newc_FirstName", DataSubmitted.newc_FirstName)
      args.add("newc_LastName", DataSubmitted.newc_LastName)
      args.add("newc_Salutation", DataSubmitted.newc_Salutation)
      args.add("newc_Contact", DataSubmitted.newc_Contact)
      args.add("newc_Address", DataSubmitted.newc_Address)
      args.add("newc_Town", DataSubmitted.newc_Town)
      args.add("newc_Province", DataSubmitted.newc_Province)
      args.add("newc_PostalCode", DataSubmitted.newc_PostalCode)
      args.add("newc_Country", DataSubmitted.newc_Country)
      args.add("newc_HomePhone", DataSubmitted.newc_HomePhone)
      args.add("newc_WorkPhone", DataSubmitted.newc_WorkPhone)
      args.add("newc_CellPhone", DataSubmitted.newc_CellPhone)
      args.add("newc_SatPhone", DataSubmitted.newc_SatPhone)
      args.add("newc_VesselPhone", DataSubmitted.newc_VesselPhone)
      args.add("newc_FaxNumber", DataSubmitted.newc_FaxNumber)
      args.add("newc_OtherPhone", DataSubmitted.newc_OtherPhone)
      args.add("newc_EmailAddress1", DataSubmitted.newc_EmailAddress1)
      args.add("newc_EmailAddress2", DataSubmitted.newc_EmailAddress2)
      args.add("newc_Notes", DataSubmitted.newc_Notes)

      vSelect = evaluate_string(vSelect)
      IF cn.execute(vSelect,args) = .f.
      vErrorMsg = cn.callresult.text + crlf() + vSelect
      save_to_file(vErrorMsg,"c:\t2.txt")
      vErrorMsg = ""
      END IF
      vClientid = cn.LastInsertedIdentity()

      END IF

      '==LISTING INSERT
      '==GET NEXT NUMBER
      dim next_filenumber as c
      delete session.next_filenumber
      dim session.next_filenumber as c

      if vbrokeragecompany = "TriNav"
      next_filenumber = sql_lookup(cn,"defaults","id=1","next_filenumber")
      else
      next_filenumber = sql_lookup(cn,"defaults","id=1","next_athearn_filenumber")
      end if

      if vbrokeragecompany = "TriNav"
      sql_update = "update Defaults set next_filenumber = "+increment_value(next_filenumber)
      else
      sql_update = "update Defaults set next_athearn_filenumber = "+increment_value(next_filenumber)
      end if
      IF cn.execute(sql_update)= .f.
      vErrorMsg = cn.callresult.text + crlf() + vSelect
      'ErrorOut() ' prints error halt progress
      END IF

      'add listing
      if vbrokeragecompany = "TriNav"
      next_filenumber = next_filenumber + fdlocn
      else
      next_filenumber = fdlocn + next_filenumber
      end if

      ' save_to_file(fdlocn,"c:\fd.txt")
      ' save_to_file(next_filenumber,"c:\nf.txt")
      session.next_filenumber = next_filenumber ' to show in message page

      vSelect =<<%txt%
      INSERT INTO tblFileDetail
      (
      FileNumber,
      VesselID,
      ClientID,
      BrokerageCompanyName,
      Category,
      BrokerName,
      ListingLocation
      )
      VALUES
      (
      :next_filenumber,
      :vvesselid,
      :vclientid,
      :fd_Brokerage,
      :fd_Category,
      :fd_BrokerName,
      :fd_ListLocn
      )
      %txt%

      args.add("next_filenumber", next_filenumber )
      if vVesselid = 0
      args.add("vvesselid",1,sql::argumentusage::inputargument,.t.)
      else
      args.add("vvesselid", vvesselid)
      end if
      if vClientID = 0
      args.add("vclientid",1,sql::argumentusage::inputargument,.t.)
      else
      args.add("vclientid", vclientid)
      end if
      args.add("fd_brokerage", DataSubmitted.fd_BrokerageCompanyName)
      args.add("fd_category", DataSubmitted.fd_Category)
      args.add("fd_brokername", DataSubmitted.fd_BrokerName)
      args.add("fd_listlocn", DataSubmitted.fd_ListingLocation)

      vSelect = evaluate_string(vSelect)
      IF cn.execute(vSelect,args) = .f.
      vErrorMsg = cn.callresult.text + crlf() + vSelect
      save_to_file(vErrorMsg,"c:\t3.txt")
      vErrorMsg = ""
      END IF

      cn.close()

      END WITH

      END FUNCTION
      Steve Wood
      See my profile on IADN

      Comment


        #4
        Re: My Concern for SQL Injection

        Wow Steve, that was quite an example. Not sure I understand it all but it's a good start. Thanks!!!

        My past experience when working with SQL Injection involved listing all the "harmful" characters or "phrases" that I thought a hacker might try to use, such as "<script", "?", "varchar", "drop", "=" etc. I see nothing like that in your example and I only
        wonder (for clarity sake) is that type of checking done in one of the supplied functions that I am now currently not familiar
        with. If so, which function parses that list and is it accessable to me, the developer?

        Comment


          #5
          Re: My Concern for SQL Injection

          It is my understanding that using arguments thwarts sql injection without any added work on our part. I dont know if we have access to the guts of args.add; you can read up on them by searching for 'arguments' in the Documentation. Arguments are also good for formatting dates. Its hard to keep up with how each database wants their dates formatted, so using an argument will auto-format the date for the target database.

          now() looks like this: "08/18/2012 04:52:19 65 pm" but that would not be valid for entry in MySQL, but using an argument like below will format it for me:

          args.add(mydate,now())
          Steve Wood
          See my profile on IADN

          Comment


            #6
            Re: My Concern for SQL Injection

            As Steve says, arguments are designed to prevent injection attacks. At least that's what they tell us. I guess you could test it to verify. Sometimes I have gotten lazy and not used args, but as Steve points out and as I discovered, sql doesn't always like what you try to pass into the db, so using args from the get-go will save you headaches when sql might otherwise reject your data entry or query.
            Peter
            AlphaBase Solutions, LLC

            [email protected]
            https://www.alphabasesolutions.com


            Comment


              #7
              Re: My Concern for SQL Injection

              I've been using the Xbasic Validation property for fields that I worry are susceptible to injection. Here's one where I am validating the characters entered:

              Code:
              function validate_SUB_CODE_TITLE as p (e as p)
              	
              if e.data.SUB_CODE_TITLE = "" then
                 validate_SUB_CODE_TITLE.hasError = .t.
                 validate_SUB_CODE_TITLE.errorText = "The Cost Code title cannot be blank."
              else
              
              	dim validChars as c 'note: the first allowed character is a space
              	 validChars =" _()-.,&#abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
              	
              	dim s as c
              	 s = e.data.SUB_CODE_TITLE
              	dim i as n
              	dim c1 as c 
              	dim result as c
              	 result = "True"
              	 
              	for i = 1 to len(s)
              	  c1 = mid(s, i, 1)
              	  if occurs(c1, validChars) = 0 then
              	   result = "False"
              	  end if
              	 next
              	
              	 if result = "False" then
              	   validate_SUB_CODE_TITLE.hasError = .t.
              	   validate_SUB_CODE_TITLE.errorText = "The only special characters allowed are period, comma, underscore, hyphen, parenthesis, amper sign (&),  or number sign."
              	 end if
              end if
               
              end function
              For memo (text area) fields, I also have the Xbasic function (below) named remove ScriptTags (that Selwyn helped me with) and it is called by the CanUpdateRecord or CanInsertRecord server side events with this line:

              Code:
              dataSubmitted.COST_CODE_NOTES = removeScriptTags(dataSubmitted.COST_CODE_NOTES)
              Code:
              function removeScriptTags as c (html as c )
              'PURPOSE - To remove javascript from HTML.  In case users try to inject javascript there.
              'WRITTEN BY:  Selwyn Rabins
              
              'debug(1)	
              dim count as n 
              count = occursi("<SCRIPT",html) 
              dim i as n 
              dim search as c 
              for i = 1 to count 
               search = extract_string(html,"<SCRIPT","</SCRIPT>",1,.t.)
               html = stritran(html,search,"")
              next i 
              
              
              count = occursi("&lt;SCRIPT",html) 
              for i = 1 to count 
              	search = extract_string(html,"&lt;SCRIPT","&lt;/SCRIPT&gt;",1,.t.)
              	html = stritran(html,search,"")
              next i 
              
              
              removeScriptTags = html
              
              
              end function
              Carol King
              Developer of Custom Homebuilders' Solutions (CHS)
              http://www.CHSBuilderSoftware.com

              Comment

              Working...
              X