SQL statement with parameters (1 Viewer)

[deathcake]

Hi,

I've done extensive searching over the last hour and I can't seem to find what i'm after, or if it's possibly the way i'm doing it. I want to take the output of a text control and insert this value into a table. I have been reading up on SQL injection prevention so I'm going to use parameters in my SQL statement instead.

Private Sub Command7_Click()

    Dim sqlString As String

    sqlString = "INSERT INTO dbo_title(Title_JobTitle) VALUES(@JobTitle)"

    CurrentDb.Execute sqlString
    

End Sub

I was trying to keep it simple by just issuing the SQL statement straight to the database. I have an auto increment primary key, so all i'm needing to insert is the job title. I'm struggling to find how to create my parameter, "JobTitle" though...

 

[pbaldy]

Well, the normal method would be:

sqlString = "INSERT INTO dbo_title(Title_JobTitle) VALUES('" & FormReferenceHere & "')"

which presumes the field has a text data type.

 

[deathcake]

Hi pbaldy,

I did consider that but doesn't that leave your database open to SQL injection where someone writes in SQL code into the field and it's executed as part of the query?

 

[pbaldy]

It's not something I've ever experienced, but I suppose it's possible. I'd be more worried with a web application than something internal. If you're worried about it, you could do some sort of validation on the textbox contents before executing SQL.

 

[deathcake]

Fair enough! I suppose I could exclude any brackets and ' " characters.

Out of shear curiosity then, can a parameter be added to the SQL statement as I have done, or would I need to go down the route of creating a DAO query and adding the parameters to that?