SQL security

[SkiNLaB]

Basically i have an option in my program where users can write their own sql, but i want to force it to allow only select queries

so i just make sure the first word is select.

is there anyway that someone can do an update, insert, or delete but having the first word as 'SELECT'

??

[brucevde]

Yes, the Select...Into syntax can be used to create a new table, for example Select * Into EmployeeBackup From Employees.

How do you execute your queries? Depending on the ADO provider, it is possible to execute multiple statements in one call, if you separate the statements with a semi-colon.

for example the following (untested, based on what I read) code works using the SQLOLEDB provider.

Dim objDB As ADODB.Connection
 
Set objDB = New ADODB.Connection
 
objDB.Open "provider=sqloledb;data source=server1;initial catalog=northwind;integrated security =sspi"
 
objDB.Execute "Select * From Employees ; Delete * From Employees"
 
objDB.Close
Set objDB = Nothing

There are alternatives. You could create a better security model for your database. Create Views to the underlying tables. Users then select from the View instead.

[SkiNLaB]

Im using a mySQL database so views aren't a possibility. I am using ADO, i must test that double query thing, i suspect your right.

As for select * into.

is that a make - table query ? ie, that will make a new table not add into an existing one?

[brucevde]

Yes, it is a make-table query.