Encoding strings to SQLServer and reading them back

  1. 7 months ago

    Richard S

    8 May 2019 Pre-Release Testers, Xojo Pro

    I cant seem to get my head around Encodings...
    I have a desktop app w/textArea for user to enter a comment.
    I post that to an MSSQL database varchar field.

    If the user input includes a single quote in the comment it posts to the database and I can see the single quotes in the MS management console tool.

    However when I later requery the database and assign the comment back to the a textarea, the results vary as follows:

    //strips the single quotes in the textarea
    s = rs.Field("Comment").StringValue

    //Each single quote appears but has an  before them (thats A with circumflex).
    s = rs.Field("Comment").StringValue.DefineEncoding(Encodings.WindowsLatin1)
    s = rs.Field("Purpose").StringValue.DefineEncoding(Encodings.WindowsLatin1).ConvertEncoding(Encodings.UTF8)
    s = rs.Field("Purpose").StringValue.DefineEncoding(Encodings.WindowsLatin1).ConvertEncoding(Encodings.UTF8).toText

    What do I need to do differently to post strings that include quotes to the database and then read them back to display correctly?
    Ive tried many combinations of Encoding defines and conversions on both the sql UPDATE and on the SELECT side and cant seem to tumble onto the solution.

    (if it helps...) My sql server tells me its Collation is SQL_Latin1_General_CP1_CI_AS and Im using the SQLDatabaseMBS plugin with the FreeTDS ODBC driver)

    I wonder if your single-quote is not in fact an ASCII single-quote (Octal 047), but a higher Unicode character?

    In which case you should be storing the text in SQL Server as an nvarchar (Unicode supported) column datatype and not a varchar (does not support Unicode characters).

    I find when Unicode characters get saved to a varchar column, the double-byte values get stripped or corrupted in unexpected ways giving you funny characters results like this.

    As a rule of thumb, any text field that is editable by a user should be saved to an nvarchar column in SQL Server (because you never know what people will type ;-).

    I hope that helps.

  2. Emile S

    8 May 2019 Europe (France, Strasbourg)
    Edited 7 months ago

    Are-you wrinting data in the db using code that looks likes:

    Dim row As New DatabaseRecord
    // ID will be updated automatically
    row.Column("Name") = "Penguins"
    row.Column("Coach") = "Bob Roberts"
    row.Column("City") = "Boston"

    If so, you have to change it. I am searching the correct advice.

    I found it. Read there:
    http://docs.xojo.com/PreparedSQLStatement
    http://docs.xojo.com/SQLitePreparedStatement

    As far as I recall, these will take good care of the single quote.

    Prepared statements also mitigate the risk of SQL injection in web apps.
  3. Richard S

    8 May 2019 Pre-Release Testers, Xojo Pro

    The update side looks like this:
    sql = "UPDATE " + Preferences.DiveTableName + " Set " sql = sql + " Purpose=:field1" sql = sql + " Where id = :fkey" dim p as SQLPreparedStatementMBS = Data.DB.Prepare(sql) p.BindType("field1", p.kTypeString) p.BindType("fkey",p.kTypeLong) //p.Bind("field1", purpose) //this didnt fix it p.Bind("field1", purpose.DefineEncoding(Encodings.UTF8).ConvertEncoding(Encodings.WindowsLatin1) ) //neither does this p.Bind("fkey",me.PK) try p.SQLExecute catch r as RuntimeException.

  4. Emile S

    8 May 2019 Europe (France, Strasbourg)

    Using .Bind is meant to avoid troubles with single quotes… (people in this Forum told me that years ago).

    When I do that (below), I was in a hurry (I had to deliver the application):
    At .LostFocus time, I replace the standard single quote with the single curly quote and I stopped having the trouble at write DataBaseRecord time.

    Another idea someone ?

    PS: Encodings.WindowsLatin1 what a strange idea… Why don’t you use UTF-8 (the Xojo native Encoding) ?

  5. Scott C

    8 May 2019 Pre-Release Testers, Xojo Pro Answer Vancouver, Canada
    Edited 7 months ago

    I wonder if your single-quote is not in fact an ASCII single-quote (Octal 047), but a higher Unicode character?

    In which case you should be storing the text in SQL Server as an nvarchar (Unicode supported) column datatype and not a varchar (does not support Unicode characters).

    I find when Unicode characters get saved to a varchar column, the double-byte values get stripped or corrupted in unexpected ways giving you funny characters results like this.

    As a rule of thumb, any text field that is editable by a user should be saved to an nvarchar column in SQL Server (because you never know what people will type ;-).

    I hope that helps.

  6. Richard S

    8 May 2019 Pre-Release Testers, Xojo Pro

    I converted the column to nvarchar per Scotts suggestion and it's looking pretty good. I think that is the solution. Thanks to all!

or Sign Up to reply!