Using Xojo 2013 r3.3 in Mac OS and Xojo 2015 r4.1 in Windows and Postgres 9.1
I have a bytea data field in Postgres where I am storing rtfdata in Mac. Field saves and loads perfectly in Mac. In Windows, I get a hard crash.
To load the rtfdata I use:
dim myStr as String
myStr= defineencoding(myStr, encodings.UTF8)
myStr=DecodeHex(rs.Field("body").StringValue) <---- crash in Windows occurs here
TextArea1.StyledText.RTFData = myStr
To store the rtfdata:
//Encode the text area to be saved as a blob (bytea in PostgreSQL)
dim myStr as String
myStr=WordProcessor_container1.TextArea1.StyledText.RTFData
myStr= defineencoding(myStr, encodings.UTF8)
myStr = "\\x"+EncodeHex(myStr)
row.Column("body") = mystr
[quote=334808:@Dave S]look up SQL Injection and you will…
Someone could very easily create an RTF document crafted in a manner that would wipe out your database.
Search this forum for “Little Bobby Tables”
[/quote]
So, if we encode the rtf data into hex, does that eliminate (or severely reduce) the chances of SQL Injection?
yes…
however, I would start to get used to prepared statements, as this isn’t going to be the only database application you may write, and I’d learn it now rather than later.
To be honest, it was something that I myself avoided for the longest time… but the app I am working on right now, would be either impossible or dangerous (injection wise) without prepared statements.
[quote=334686:@David Schlam]dim myStr as String
myStr= defineencoding(myStr, encodings.UTF8)
myStr=DecodeHex(rs.Field(“body”).StringValue) <---- crash in Windows occurs here
TextArea1.StyledText.RTFData = myStr
[/quote]
DefineEncoding does nothing here, since you just throw away the (empty) string anyway and replace it with a brand new string which has no encoding. Try
dim myStr as String
myStr=rs.Field("body").StringValue
myStr= defineencoding(myStr, encodings.UTF8)
myStr=DecodeHex(myStr)
TextArea1.StyledText.RTFData = myStr
DatabaseRecord uses a preparedstatement already, so the code he posted is ok.
Just an FYI for SQLite and PostgreSQL rs.edit / rs.update is much slower than the equivalent SQL. In MS Access the reverse is true. According to my tests.
I did have to change a number of records using a loop in Xojo which I did first using rs.update, and then (nothing ever goes as planned the first time) using a preparedstatement. I did notice a dramatic speed increase using preparedstatement (Postgres).
I’m a little confused though about binding. From the documentation:
[code]Dim ps As PostgreSQLPreparedStatement = _
db.Prepare(“SELECT * FROM Persons WHERE Name = $1 AND Age >= $2”)
ps.Bind(0, “john”)
ps.Bind(1, 20)[/code]
the first parameter in ps.bind, 0, binds the following parameter in ps.bind, “John”, to the first variable in postgres, $1? So, if I’m correct, the first parameter in ps.bind is zero based and the postgres parameters are 1 based.
So, it’s kind of similar to rs.edit / rs.update but you get the advantage of a dramatic speed increase.
