Loose apostrophes are killing my DB integration

  1. 2 months ago

    Amy B

    Feb 5 Marietta, Georgia, USA.

    Hey all, these apostrophes are running loose in my data and it's giving me errors when putting text into a SQLite database - is there a wrapper or some kind of way that I can install appstrophe-inclusive names / text into a SQLite database?

    prepared statements are the key.

  2. Jean-Yves P

    Feb 5 Pre-Release Testers, Xojo Pro Answer Europe (France, Besançon)

    prepared statements are the key.

  3. Amy B

    Feb 5 Marietta, Georgia, USA.

    Thanks, looked it up and will try it. Thought those were only avail for MySQL.

  4. Dave S

    Feb 5 San Diego, California USA

    Prepared Statements also protect from SQL Injection as well as allowing "special" characters such as single/double quotes

  5. Tim S

    Feb 6 Canterbury, UK

    I'm using SQLite. I had my own wrapper for the functions I need so I could log any issues in the same place I log everything else. Extending that for prepared statements turned out to be simple enough - and necessary as I was starting to have the same issues as the OP.

  6. Jean-Yves P

    Feb 6 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    it's just a shame they don't use the same syntax for different databases.

  7. Richard D

    Feb 6 Pre-Release Testers, Xojo Pro Europe (UK, London)

    @Jean-YvesPochez it's just a shame they don't use the same syntax for different databases.

    especially between sqlite and cubesql. i want to use prepared statement but have to cater for both since my application can be single user or multi user with just a extra file which i named xxlogin.rsd

  8. Bob K

    Feb 6 Pre-Release Testers, Xojo Pro Kansas City

    ActiveRecord doesn't care either since it's using preparedStatements in the background and you don't have to write any of the insert or update statements. Code is like this:

    myRecord = New Data.User
    myRecord.FirstName = "Bob"
    myRecord.LastName = "O'Neil"
    myRecord.save
  9. Jean-Yves P

    Feb 6 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    I've done this also for my database libraries Bob, it's just something you plan when you start a database library
    not something you plan when you start sqlite and then go to mysql or postgres ...

  10. Ivan T

    Feb 6 Pre-Release Testers

    @Bob K ActiveRecord doesn't care either

    You should also include a Link to the What is ActiveRecord and ARGen

  11. Bob K

    Feb 6 Pre-Release Testers, Xojo Pro Kansas City

    @Ivan T You should also include a Link to the What is ActiveRecord and ARGen

    Sorry, I try not to pimp my products too much because I know it can be annoying. But since you asked:

    ARGen the ActiveRecord generator. In freebie mode you're limited to generating two tables at a time. Licensed lets you generate all the tables and starter UI you want. Work with desktop and web projects. For iOS only SQLite databases and no UI.

  12. David C

    Feb 7 Pre-Release Testers, Xojo Pro Derby, ITM

    The situation is worse than you can imagine, if you don't use Prepared Statements. Not only are there different SQL syntaxes, but the syntax varies inside the LIKE command too. I also use it to clean up JavaScript text!

    As a result I created a method to clean up the best I could, catering to both general SQL commands and the LIKE command on each platform:

    Protected Function getSQLFormatWAD(SQLBrand As String, myFieldValue As String, LIKEcommand As Boolean = False) as String
      'Convert string containing a single quotation or slash into escape codes
      
      select case SQLBrand
      case "DB2", "PostgreSQL", "Postgres"
        myFieldValue = ReplaceAll(myFieldValue, "'", "''") 'double the single quote
        if LIKEcommand then
          myFieldValue = ReplaceAll(myFieldValue, "\", "\\") 'this causes DOS paths in fields to not be recognised
          myFieldValue = ReplaceAll(myFieldValue, ";", "\;") 'semi-colons may be used to put multiple statements on one line, so make it a literal
          myFieldValue = ReplaceAll(myFieldValue, "%", "\%") 'percentages might muck up LIKE if they're in the string being searched
          myFieldValue = ReplaceAll(myFieldValue, "_", "\_") 'underscore can act as a single character wild card in Postgres
        end if
        Return DefineEncoding(myFieldValue, Encodings.UTF8)
        
      case "SQLite", "CubeSQL"
        myFieldValue = ReplaceAll(myFieldValue, "'", "''") 'double the single quote
        Return DefineEncoding(myFieldValue, Encodings.UTF8)
        
      case "MySQL"
        myFieldValue = ReplaceAll(myFieldValue, "'", "\'") 'escape the single quote
        if LIKEcommand then
          myFieldValue = ReplaceAll(myFieldValue, "\", "\\")
          myFieldValue = ReplaceAll(myFieldValue, "'", "\'") 'escape the single quote
          myFieldValue = ReplaceAll(myFieldValue, ";", "\;") 'semi-colons may be used to put multiple statements on one line, so make it a literal
        End If
        Return DefineEncoding(myFieldValue, Encodings.UTF8)
        
      case "MSSQL"
        myFieldValue = ReplaceAll(myFieldValue, "'", "''") 'double the single quote
        Return DefineEncoding(myFieldValue, Encodings.UTF8)
        
      case "JavaScript"
        'myFieldValue = ReplaceAll(myFieldValue, "fi", "") 'Shift-Option-5 fails to import in HTML
        'myFieldValue = ReplaceAll(myFieldValue, "fl", "") 'Shift-Option-6 fails to import in HTML
        'myFieldValue = ReplaceAll(myFieldValue, "˘", "") 'Shift-Option-> fails to import in HTML
        myFieldValue = ReplaceAll(myFieldValue, "'", "\'") 'escape the single quote
        Return DefineEncoding(myFieldValue, Encodings.UTF8)
        
      case else
        myFieldValue = ReplaceAll(myFieldValue, "'", "''") 'double the single quote
        Return DefineEncoding(myFieldValue, Encodings.UTF8)
        
      end select
      
      Exception err
        
    End Function

or Sign Up to reply!