escape sql statements (1 Viewer)

[dreamdelerium]

hi everyone. when i send an sql statement to update a table or add a record i am using the replace function [ie, replace (something, "'", "''")] to remove all single quotes from my string. are there other characters i need to remove as well that might cause problems? here is an example of my insert strings:

For i = 0 To 186
InsertString = InsertString & "Q" & Format(i, "000") & ","
Next i
GetLength = Len(InsertString) - 1
InsertString = Left(InsertString, GetLength)
For i = 0 To 186
InsertString1 = InsertString1 & Replace(Forms![frmSection1].Controls("Q" & Format(i, "000")).Value, "'", "''") & "','"
Next i
GetLength = Len(InsertString1) - 1
InsertString1 = Left(InsertString1, GetLength)
MyNewString = "Insert into tblSection1 (QuestionaireID,DistrictID,FacilityID,SectionID,StudyID," & InsertString & ") " _
& "values ('" & q1 & "','" & GetDistrict & "','" & GetFacility & "','1','" & GetCode & "','" & InsertString1 & ")"
 
GetLength = Len(MyNewString) - 2
MyNewString = Left(MyNewString, GetLength)
MyNewString = MyNewString & ")"
MyNewString = MyNewString
buildMyStringForm1 = MyNewString

i know it looks a bit screwed up but this was the fastest way to create an insert sql statement with 187 inserts. thanks for the help
jason

 

[namliam]

having a table with columns Q001, 002, 003,...., 186 smells like something that NEEDS redesigning...

Since you are enclosing your strings inside the SQL by ' you only have to escape/replace the ', nothing more nothing less.

 

[dreamdelerium]

that was the best way i could think of to design it. if you know a better way id appreciate the help. the database is meant to capture responses to a survey. the survey has 7 sections. each section has about 50-200 questions. i have 8 tables (one table as the master table, 7 as children, one for each section) so, when i want to insert the responses into a table (depending on what section is being saved) i run an sql command similar to the one i posted earlier. this way i prevent mistakes in the sql statements, its quick, and i can reuse the code for every section with minor changes.

anywho, the point of my last post was to ask if there are other charachters that i need to be aware of that could cause problems when run in an sql statement

 

[namliam]

OK, So the design I would suggest is something like:
Master Key, Section NR, Question NR, Answer

This way you only have one table with limitless sections, limitless questions. Instead of beeing limited to 7 tables/sections with again a limited number of questions inside it.

anywho, your orignal question I answered in my previous post as well...

Since you are enclosing your strings inside the SQL by ' you only have to escape/replace the ', nothing more nothing less.