db error :: near "t": syntax error

[code] sql = "UPDATE [userSettings] SET " _
+ “[strPresentation] = '” + strVar + “’” _
+ " WHERE [intUserID] = " + str( Area51.intUserID )

  System.DebugLog CurrentMethodName + " :: " + sql

This is a one-liner! It works if I enter something in clear text in the UPDATE statement, such as “hello world!!”.

SQL = "Create Table [userSettings] (intID Integer PRIMARY KEY AUTOINCREMENT, intUserID Integer, dteCreated timeStamp, " _ + " strFirstName varChar, strLastName varChar, strCompany varChar, strURL varChar, " _ + " dteSubscriptionStart timeStamp, dteSubscriptionEnd timeStamp, "_ +" intCountryID Integer, intRegionID Integer, strLanguageID varChar, strPresentation TEXT )" db.SQLExecute(SQL)

It used to be varChar, but i read that TEXT also was an option and it was earlier discussed on this board as well.
My question is, what does “near “t”: syntax error” means!?

Sometimes the error message is meaningless: it only means that you have an error in your db related code.

as an example: why do you pass a string when you tell the db engine it is an integer ?

" WHERE [intUserID] = " + str( Area51.intUserID)

I suppose it will work with sqlite, but…

What I usually do, in this kind of case(s) is to check (and recheck) carefully the “offending code” (and some lines before) and I usually found my error.

Did you try to place the code in a single line ?
(this also helps sometimes)

[code]" WHERE [intUserID] = " + Area51.intUserID/code]

Expected String, got Int64

Trust me!

The single line may work sometimes. However, sometimes has never been present to me so far. Not at this moment or the past 16 years or so…!

Thanks for the input. I’ll think of something…

The presentation is text from the web. Maybe there are hidden characters inside the line!?

DIM trimText as string trimText = ReplaceLineEndings(strVar, EndOfLine.Windows) sql = "UPDATE [userSettings] SET " _ + " [strPresentation] = '" + trimText + "' " _ + " WHERE [intUserID] = " + str( Area51.intUserID )
Same error! :slight_smile:

debug it, stop after the sql=
and tells us what is inside the SQL variable (and not the program code to do it)
sure there is a sql syntax error near a “t” char…

[code]Area51.checkUserPresentation :: UPDATE [userSettings] SET [strPresentation] = 'Hello!
This account is a fake account, for testing purpose only.

Don’t contact me for any SEO.

The SEO is fake and for testing purpose only.

The website, however, if for real!

Have a good day and good luck in the search for a real and good SEO Expert!’ WHERE [intUserID] = 568
11:22:05 AM
Area51.checkUserPresentation db error :: near “t”: syntax error[/code]

I think this is the problem. The lines are split and not in one row, with the line endings included… "T"his account is fake! actually begin with the letter “t”.

Ha! God, this is new to me!! I sure didn’t expected this to happen!! INSERT, UPDATE and DELETE has been my working companions for… since the spring of 1999.

replaceAll( strVar, ch(10) + ch(13), EndOfLine)

Help me! How should it be!

I also tried to send the text as ISO and UTF-8, but same result.

I think I need to convert the text to UTF-8, as described on this page: http://www.regular-expressions.info/realbasic.html

However, not now. Time call for a small break!
I’ll look into that in the afternoon. I’m pretty sure the New Line is the problem.

I have found that you should not use the EndOfLine character in a database field.

I ReplaceAll on any string that I place in a database by replacing the EndOfLine with the pipe character (¦) and storing that. Upon retrieving the string from the database I replace the pipe character with EndOfLine - problem solved.

You need to escape the text in the update.

Since the string starts and ends with a ’ character, guess where SQL thinks the statement ends when it encounters

Don’t contact me for any SEO.

It thinks the t is the start of a new statement

You need to take the string and replace ’ with two such characters

  • ReplaceAll(strVar,"’","’’") +

Well spotted!

I looked for that but didn’t see it!

Sorry Jakob, this hurts me! I have to say, that you’re using a very bad programing style regarding to security (SQL Injection) but also in matters like object reusing and code maintenance.

Just a suggestion (please keep in mind i am writing this just down, so no warranty and keep also in mind that DatabaseRecord <> Recordset)

Create an Object Class for your Table e.g. when using a Table named “Contacts” create same Object Class in Xojo with Properties referenced to each your database fields (Street, Name, ZIP, Phone etc.)

and 1 additional method :

CreateFromDatabaseRecord(rs as Recordset)

and 2 additional functions:

LoadFromDB(id as Integer) as Boolean ToDatabaseRecord as DatabaseRecord SaveToDB as Boolean

So the magic comes here:

LoadFromDB(id as Integer)
This function loads a recordset from your Database with given ID value.
Additionally I am using for all my DB IO stuff another object class named “MyCore” where I just throw my SQL Query in and it handles the DB Provider automatically, so I can switch easily databases (SQLlite, mysql, ODBC) :

dim WasSuccessful as Boolean = false rs = myCore.SQL("SELECT * FROM YourTable WHERE Identifier=" + str(id)) if rs <> nil then WasSuccessful = CreateFromRecordset(rs) return WasSuccessful

Return value shows me if loading of recordset was successful or not. So I am using this class like this:

if MyObject.LoadFromDB(4711) then // do some stuff like filling input fields ... InputField.Text = MyObject.MyPropertName end if

CreateFromDatabaseRecord(rs as Recordset)
This method basically transfers your DB fields from referenced Recordset to your object properties.

Me.PropertyName = rs.Field("DBFIeldname").DoubleValue (or Stringvalue depends on your type)

Dont forget to define Encodings here when reading strings:

Me.PropertyName = DefineEncoding(rs.Field("DBFIeldname").getString, Encodings.UTF8)

And now back to your database. In your main program just call this method in order to save with current values:

if myObject.SaveToDB then
// Show info for successful saving or reset your form or do sth else
// show error Message
end if

SaveToDB as Boolean
This functions saves your object back to DB. It first checks if your ID Property is not empty (=0) because when this is empty I assume that the object is a new one without unique ID:

try dim WasSuccessful as Boolean = false if Me.Identifier = 0 then // insert new recordset into DB db.InsertRecord("YourTable", Me.ToDatabaseRecord) WasSuccessful = not db.Error else // update existing recordset dim rs as Recordset rs = db.SQLSelect("SELECT * FROM YourTable WHERE Identifier=" + str(Me.Identifier)) if rs <> nil then rs.Edit rs.Field("PropertyName").StringValue = Me.PropertyName // all your Properties and DataTypes rs.Update WasSuccessful = not db.Error end if end if return WasSuccessful catch err as NilObjectException Finally end try

ToDatabaseRecord as DatabaseRecord
This function transfers all your Properties back to DatabaseRecord for Updating.

dim rs as new DatabaseRecord rs.DoubleColumn("DBFIeldname") = MePropertyName ... return rs

Reuse this snippets als kinda framework for all your DB projects, extent it with other functions like seamless DB Provider switching, checking against timestamp if recordset altered meantime or more optimizations.

Thats far better way to deal with database operations.

Jeff! Thanks for the reply!
I sort of suspected this… but this was never the case earlier, in other projects. Not like this. my brain was simply not “all in”!! :slight_smile:

Thank you for taking the time to write so much!
For me, KISS is the leading star…
I’ll look into this one day!
Security is not an issue. It is solved.

use a preparedstatement instead of direct string sqlselect
it will filter these sql unwanted chars automatically

There is already such feature in the project. Just that I didn’t call it…!! How silly!! :slight_smile:

I’m aware of this, believe it or not!

' ## SQL INJECTION DIM tmp as String tmp = trim(strTmp) tmp = replaceAll(tmp , "|", "") tmp = replaceAll(tmp, "'", "''") 'System.DebugLog CurrentMethodName + " :: " + tmp return tmp

your method doesn’t help lad.
In case of % or ? placeholders the query would be:

SELECT * FROM Contacts WHERE username = ‘something’ and password = ‘%%’

Avoid direct SQL String operations, use Prepared Statements or Proxy Objects.
Never code quick and dirty style. It’s not a rule, it’s a habit.

sql = "UPDATE  [userSettings] SET " _
      + "[strPresentation] = '" + strVar + "'"  _
      + " WHERE [intUserID] = " + str( Area51.intUserID )
      System.DebugLog CurrentMethodName + " :: " + sql

Just to help out a bit, this code should be something like this when using PreparedStatements:

(Pseudo code…)

Dim ps as SQLitePreparedStatement
ps = dbDatabase.Prepare("UPDATE [userSettings] SET [strPresentation]=? WHERE [intUserID]=?")
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, strVar)
ps.Bind(1, Area51.intUserID)

...the important(!) error checking.

this is the problem