SQL Syntax Question

If I want to do a SELECT on a table where I set the WHERE clause to a variable from my program, I structure the SQL command string like this:

"SELECT * FROM Notes WHERE CaseID='" + cID + "'"

… where the value of cID is supplied by my program and everything works fine.

How would I structure the syntax for an UPDATE command in a similar manner if I have a string (s) that I want to save to the column “NotesData” in the table “Notes”. As close as I can get from trying different variations is what you see here:

"UPDATE Notes SET NotesData= '" + s + "' WHERE CaseID =" + cID

That throws a db error saying I’m missing a quote at the end of the string (and yeah, you guessed it, when I added a quote at the end of the string, it failed for another reason)

You’re missing both quotes.
And you had them originally.

So combining the two statements

“UPDATE Notes SET NotesData= '” + s + “’ WHERE CaseID =” + cID

“SELECT * FROM Notes WHERE CaseID=’” + cID + “’”

you need

"UPDATE Notes SET NotesData= '" + s + "' WHERE CaseID ='" + cID + "'"

Note that this method leaves you open to what is called SQL injection attacks if the values of s or cID can be freely typed into a screen by a user.

I’m guessing that s contains an apostrophe.

This code is very error prone and potentially very dangerous. It should be changed to:

Dim ps As PreparedStatement = db.Prepare("UPDATE Notes SET NotesData=? WHERE CaseID=?")
If db.Error Then
    // Handle Error
End If

ps.SQLExecute(s, cID)
If db.Error Then
    // Handle Error
End If

Please read: http://en.wikipedia.org/wiki/SQL_injection

Yeah … unfortunately I know that, but the current way I’m doing it with Prepared Statements has another problem (see my other post floating around about saving long strings. I’ve already provent that I can manually save the data I need to correctly, so I wanted to try using UPDATE to see if the prepared statements were the source of my problem.

Might be, Tim … it’s a string of RTF text data that I’m running into a strange 2000 character limit problem and I’m trying different things to see if I can’t narrow down the source of the problem. I did check it once to make sure there were no literals … hard on the old eyes to make sure 2000 characters have no apostrophes ^^ … I will look again though. Thanks!

Yep … I know, but I’ve got to try some different things here to figure out what in hells bells is causing my UPDATE to max out at 2000 characters. Here’s what I had originally (with prepared statements) that’s giving me the weird “cap” problem on byte count:

dim db As SQLDatabaseMBS = App.mDB

dim ss as string
  // save the NOTES section
  for iloop As Integer = 0 to Ubound(oContainers)
    if oContainerIndex(iloop) = "nc" then
      ss = cntNotes(oContainers(iloop)).TANotes.WinRTFDataMBS
      Exit For
    end if
  //MsgBox (str(ss.Len) + EndOfLine + ss)
  // get the caseID of the current record
  Dim rs As RecordSet = db.SQLSelect("SELECT CaseID FROM caseModel WHERE (CaseName='" + caseName + "') AND (Revision='" + caseRevision + "')")
  Dim cIDnum As Integer = rs.Field("CaseID").IntegerValue
  //Dim cID As String = Str(cIDnum)
  Dim cID As String = Str(cIDnum)
  // save the Notes
  Dim sqln As String = _
  "UPDATE Notes                       " + _
  "        SET NotesData = :nts    " + _
  " WHERE CaseID = :cID;          "
  dim vn as Variant = db.Prepare(sqln)
  If db.Error Then
    // open Modal MsgBox window
    taMessage = "UPDATE Model General Record Set  PREPARE NOTES Data DB Error: " + db.ErrorMessage
  End If
  dim pn as SQLPreparedStatementMBS = vn
  pn.BindType("nts", SQLPreparedStatementMBS.kTypeString)
  pn.BindType("cID", SQLPreparedStatementMBS.kTypeLong)
  pn.Bind("nts", c.Text)
  pn.Bind("cID", cIDnum)
  If db.Error Then
    // open Modal MsgBox window
    taMessage = "UPDATE Model General Record Set SAVE NOTES  Data DB Error: " + db.ErrorMessage
    db.Commit   // Commit the recordset to the database
  End If

Rather than trying to update it via SQL can you update it via a Recordset? It’s a little slower but it does some magic behind the scenes.

[code]dim s as string = “SELECT NotesData, CaseID FROM Notes WHERE CaseID=’” + cID + “’”
dim rs as recordset = db.sqlselect(s)
//check for errors

rs.field(“NotesData”).stringvalue = sRTFData
//check for errors[/code]

If that works, I think it would indicate that you’re not escaping all of the data you need to in the RTF string.

If it were me, unless you plan on doing some searches in the DB for strings in the RTF I’d EncodeBase64 the string and use DecodeBase64 when you pull it out of the db. That way you know with 100% certainty that it’s okay.

Ah…I see that you are using MBS SQL plugin. I’d see if Christian could help.

Thanks for the reply, Bob. My first approach (way, way back) was to use RecordSets but after I changed to the MBS SQL plugin, Christian and I found out that a little know Microsoft issue was causing a problem with using RecordSet.Edit


Christian’s words were:

[quote]I normally use OLEDB to avoid the need to install native MS SQL Client.
But it seems like you can’t have a RecordSet open with it while another query runs.[/quote]

He has since modified his plugin and it throws an Unsupported Operation Error if you attempt using RecordSet.Edit, so unfortunately, I can’t “go back there”, so to speak.

I’m not sure about the MBS SQL plugins sorry. Just an unrelated note though… you can use string constants to make your SQL a little more maintainable, i.e.

  Dim sqln As String = _
  "UPDATE Notes                       " + _
  "        SET NotesData = :nts    " + _
  " WHERE CaseID = :cID;          "

could easily be a constant, which is editable in a text area.

Doesn’t look like he’s using the RTFString, because he’s binding c.text .

BTW … for anyone who may have attempted to follow the code I posted, this line (part of what I posted) is actually as you see below in my program … I had been trying various things (including placing a large text string on the clipboard and attempting to save it) and left that incorrect variable reference in there before I copied the code to post.

pn.Bind("nts", ss)

If it’s just for testing, run your original code and replaceall one apostrophe with two apostrophes.

s = ReplaceAll(s, "'", "''")

Good eyes, Jym … You DID see it ^^

But as I posted afterwards, that (c.Text) was not really the variable used … it was a mistake on my part to not remove it from what I posted. The actual code line generating the RTF from the TextArea it gets it from is:

ss = cntNotes(oContainers(iloop)).TANotes.WinRTFDataMBS

Gave that a try too, Bob … didn’t change the price of rice in China

Then it’s not a problem with your query string. At that point it must be MBS SQL plugin and how long of a string it can use.

What happens when you use the Xojo PreparedStatement would answer that question Bob.

Thanks, Tim … tried that once already … but went ahead and tried it again … here’s the text it displays with the error message (I did double-check the string to see that literals were escaped … and they were … don’t know why they appear not to be in the error message text):

[quote]DB Error: 42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Unclosed quotation mark after the character string '{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Arial;}{\f1\fnil\fcharset0 Segoe UI;}{\f2\fnil\fcharset2 Symbol;}}
{\colortbl ;\red34\green30\blue31;\red0\green0\blue0;}
{\\generator Msftedit;}\viewkind4\uc1\pard{\pntext\f2\'B7\tab}{\\pn\pnlvlblt\pnf2\pnindent0{\pntxtb\'B7}}\fi-180\li180\sl241\slmult0\cf1\lang9\fs18 For meat application: extended front sills are required for maintaining proper case temperature levels (5" baseframe = 24" or higher; 11" baseframe = 30" or higher)\par
{\pntext\f2\'B7\tab}BTUH/ft notes: \par
\pard{\pntext\f2\'B7\tab}{\\pn\pnlvlblt\pnf2\pnindent0{\pntxtb\‘B7}}\fi-180\li540\sl241\slmult0 Listed BTUH/ft indicate unlighted shelves. For LED lighting, add 36 BTUH per 4’ lighted shelf and 27 BTUH per 3’ lighted shelf to determine Total Lighting BTUH Load, then divide the Total Lighting BTUH Load by the length of the case. For T8 lighted shelves (see Appendix D) and 3rd row lighting, add 92 BTUH per 4’ lighted shelf and 69 BTUH per 3’ lighted shelf to determine Total Lighting BTUH Load, then divide the Total Lighting BTUH Load by the length of the case. \par
{\pntext\f2\'B7\tab}Rear refrigerated box increases refrigeration load by 110 BTUH/ft. \par
{\pntext\f2\'B7\tab}Add 132 BTUH/ft when aftermarket merchandising accessories are utilized to determine the total BTUH load.\par
\pn\pnlvlblt\pnf2\pnindent0{\pntxtb\'B7}}\fi-180\li180\sl241\slmult0 Listed evaporator temperature indicates unlighted shelves. For lighted shelves and/or aftermarket merchandising accessories, reduce the listed evaporator temperature by \b 2\'b0F\b0 .\cf2\lang1033\f1\par
42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Arial;}{\f1\fnil\fcharset0 Segoe UI;}{\f2\fnil\fcharset2 Sy’.
42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.[/quote]

what if you convert it to Hex and try to insert it?

Unfortunately, Jym, I can’t try that since I’m using the MBS SQL plugin and the “Prepared Statement” class in Jeremy’s response comes from the Xojo plugin.