Well done, sir. You made me feel great and miserable at the same time.
“84 deg F”… “golf tournament I played in this morning.”…!@#$%
Like I said, Kem … I haven’t found a customer yet that pays me for my golf game … 
Internally I’m pretty sure everything is using prepared statements so things like the SQL injection attempt don’t work
If you find different file a bug report
[quote=70705:@Kem Tekinay]Well done, sir. You made me feel great and miserable at the same time.
“84 deg F”… “golf tournament I played in this morning.”…!@#$%[/quote]
You can some get some of the several feet of snow I still have laying on the ground around my house (despite it having been mid 50’s for the lat 3 days and melting like mad)
More than enough still piled up around here.
Not mailing you my tractor 
A stupid reminder:
a number cannot start with a zero (0);
a text can holds a leading zero (0123456789).
I saw this “error” all the time when I was living in Cannes (a zip that starts with a zero); when ignorants puts my address in Excel, I loose the zip leading zero and the letters goes more than 1,000 Km from home (06250 goes 6250x and so goes to Arras, North of France).
In Exel, you can set a Cell type to TEXT and so no lose your leading zero
[quote]Norman Palardy
Internally I’m pretty sure everything is using prepared statements so things like the SQL injection attempt don’t work[/quote]
Norman … you threw me with that one. Are you referring to SQLite only? By “internally”, what do you mean? Within the functionality of the MS SQL database plug-in? Something else “hidden” in Xojo that I’ve never heard of or read about? What is creating the prepared statements you mention if I don’t? What I do know is that MS SQL databases will not accept the standard SQL statements I threw at it with various single/double quote characters trying to populate it. What am I missing? I might even send you a picture of me sitting outside today by my pool with my laptop doing coding here in South Carolina to “warm you up” and show my gratitude for you educating me on this point.
Just wondering if you ever followed up on this and what the results were.
Yep … still bombs on my end with literals involved … here’s a sample of some code I tried from one of my apps that exhibits the problem if I don’t replace the literals (you’ll see the first line of code in each of the Case statements has a function I have to call to do this or that line fails. “dbWorks” is a global module I have that contains, among other things, the record sets (as global properties) used in the example.
[code] App.mDB.sqlExecute(“BEGIN TRANSACTION”)
dbWorks.rsElectrical = App.mDB.SQLSelect(“SELECT * FROM electricalData INNER JOIN caseModel ON ((electricalData.CaseID = caseModel.CaseID) AND (caseModel.CaseName='” + _
caseName + “‘) AND (caseModel.Revision=’” + caseRevision + “'))”) // electrical
Dim i As Integer = 1
While Not dbWorks.rsElectrical.EOF
dbWorks.rsElectrical.Edit // prepare record set for editing
Select Case i
Case 1
if cntElectrical_ED11(oContainers(k)).txf(0).Text.Len > 0 then dbWorks.rsElectrical.Field("Length").StringValue = tfWorks.tfReplaceLiterals(cntElectrical_ED11(oContainers(k)).txf(0).Text)
if cntElectrical_ED11(oContainers(k)).txf(2).Text <> "---" then dbWorks.rsElectrical.Field("FansPerCase").StringValue = cntElectrical_ED11(oContainers(k)).txf(2).Text
if cntElectrical_ED11(oContainers(k)).txf(4).Text <> "---" then dbWorks.rsElectrical.Field("FansHiEff120Amps").StringValue = cntElectrical_ED11(oContainers(k)).txf(4).Text
if cntElectrical_ED11(oContainers(k)).txf(6).Text <> "---" then dbWorks.rsElectrical.Field("FansHiEff120Watts").StringValue = cntElectrical_ED11(oContainers(k)).txf(6).Text
if cntElectrical_ED11(oContainers(k)).txf(8).Text <> "---" then dbWorks.rsElectrical.Field("ACHeater120Amps").StringValue = cntElectrical_ED11(oContainers(k)).txf(8).Text
if cntElectrical_ED11(oContainers(k)).txf(10).Text <> "---" then dbWorks.rsElectrical.Field("ACHeater120Watts").StringValue = cntElectrical_ED11(oContainers(k)).txf(10).Text
Case 2
if cntElectrical_ED11(oContainers(k)).txf(1).Text.Len > 0 then dbWorks.rsElectrical.Field("Length").StringValue = tfWorks.tfReplaceLiterals(cntElectrical_ED11(oContainers(k)).txf(1).Text)
if cntElectrical_ED11(oContainers(k)).txf(3).Text <> "---" then dbWorks.rsElectrical.Field("FansPerCase").StringValue = cntElectrical_ED11(oContainers(k)).txf(3).Text
if cntElectrical_ED11(oContainers(k)).txf(5).Text <> "---" then dbWorks.rsElectrical.Field("FansHiEff120Amps").StringValue = cntElectrical_ED11(oContainers(k)).txf(5).Text
if cntElectrical_ED11(oContainers(k)).txf(7).Text <> "---" then dbWorks.rsElectrical.Field("FansHiEff120Watts").StringValue = cntElectrical_ED11(oContainers(k)).txf(7).Text
if cntElectrical_ED11(oContainers(k)).txf(9).Text <> "---" then dbWorks.rsElectrical.Field("ACHeater120Amps").StringValue = cntElectrical_ED11(oContainers(k)).txf(9).Text
if cntElectrical_ED11(oContainers(k)).txf(11).Text <> "---" then dbWorks.rsElectrical.Field("ACHeater120Watts").StringValue = cntElectrical_ED11(oContainers(k)).txf(11).Text
End Select
dbWorks.rsElectrical.Update // UPDATES ONLY THE CURRENT RECORD (where the pointer is currently) in the record set
If App.mDB.Error Then
App.mDB.Rollback // rollback any changes because of error
// open Modal MsgBox window
tfWorks.taMessage = "UPDATE Model Electrical Record Set (saveToDB_ELEC) Data DB Error: " + App.mDB.ErrorMessage
wndModalMsgBox.ShowModal
else
App.mDB.Commit // Commit the recordset to the database
End If
dbWorks.rsElectrical.MoveNext
i = i +1
Wend[/code]
Yes I’m pretty sure the plugins use prepared statements when you use DatabaseRecords to do inserts or edits
We were talking about InsertRecord, not Update, and while I’d expect it to be the same, I already know that there is at least some problem with bad characters in the fields when using Update.
Did you try it with InsertRecord?
Nope … thought UPDATE would be the same test (e.g., SQL command involving data transfer to db). But just tried the INSERT now and it bombs too. If I take the apostrophe out of “Joe’s”, it works fine (the commented out line) … not so with it in there though (the line that’s not commented out)
[code] // ELECTRICAL data segment of addToDB
Dim rec As DatabaseRecord
rec = new DatabaseRecord
// capture ED1 Electrical Data … If text field is empty, save NULL to the database (if recordset is missing value for this field, it treats it as NULL when saving)
If dbWorks.rsGeneral.Field(“TmpElectrical”).StringValue = “ED1” Then
App.mDB.sqlExecute("BEGIN TRANSACTION")
For i As Integer = 1 To 4
rec.Column("CaseID") = dbWorks.rsGeneral.Field("CaseID")
rec.Column("Length") = "Joe's Crab Shack"
// rec.Column("Length") = "Joes Crab Shack"
App.mDB.InsertRecord("electricalData", rec)
if App.mDB.Error then
App.mDB.Rollback // rollback any changes because of error
// open Modal MsgBox window
tfWorks.taMessage = "INSERT Model Electrical Record Data (addToDB_ELEC) DB Error: " + App.mDB.ErrorMessage
wndModalMsgBox.ShowModal
else
App.mDB.Commit // Commit the record to the database
end if
Next
End If[/code]
Thanks Norman … had no idea that was true. Doesn’t explain my results though.
So maybe there is a connection. Can you try doing your Update through a PreparedSQLStatement instead?
Sure will, Kem … it’ll be later today or maybe tomorrow morning before I can try though … working on some code here this afternoon that has to be ready for a functional review in the morning with one of my customers. As soon as wrap that up, I’ll try the prepared statement approach … my curiosity is really peaking about this one.
Me too.
Unlike you “semi-retired” guys, as my father used to say, “Retired? Why I’m part way there already … I’m tired”
I hope I die peacefully in my sleep like my grandfather. . .not screaming in terror like his passengers. – “Deep Thoughts”, Jack Handy
Ahhh, Norman … you been smoking that compost again? 