Building a WHERE clause in sql

[guest2013-1]

Heita_Hola, duuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuude

You're writing code and you're not even sure what database you'll be running it on? No man. First thing before you write any piece of code is decide which db platform you're writing for. Then you design the database with full referential integrity and THEN you write code for your program to interact with it.

You're doing it ass-way forward and it's going to cause more issues for you than it will just sitting down spending a couple of hours (or a day or two) deciding which technologies you're going to work with and what your database would look like.

@Drake2007: Even if that select statement was in a stored procedure, they'd be able to inject SQL to it that would cripple your database. I've seen it happen more than once. If I were you I'd go Google SQL Injection and see what it's all about and how a simple select statement can cause you to lose all integrity as a web developer.

 

[Drake2007]

..
@Drake2007: Even if that select statement was in a stored procedure, they'd be able to inject SQL to it that would cripple your database. I've seen it happen more than once. If I were you I'd go Google SQL Injection and see what it's all about and how a simple select statement can cause you to lose all integrity as a web developer.

No worries, I have just a simple guestbook in php and mysql and I had already protected that against injection. More pressing things to attend to than worry about it just now, so was really phishing for a quick and dirty example.

 

[Fuma]

Heita_Hola, duuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuude

You're writing code and you're not even sure what database you'll be running it on? No man. First thing before you write any piece of code is decide which db platform you're writing for. Then you design the database with full referential integrity and THEN you write code for your program to interact with it.

You're doing it ass-way forward and it's going to cause more issues for you than it will just sitting down spending a couple of hours (or a day or two) deciding which technologies you're going to work with and what your database would look like.

I thought our system should be platform independent as much as possible - nope?
I get your point but I'm using SQL Server now, but they might just decide to switch to Postgres or something. I'll try to use common sql queries as much as possible.

 

[bboy]

Private Function addToWhereSql(strSqlIn As String, strField As String, strValue As String, strSurroundythingys As String) As String
Dim strOutPut As String
If strValue <> "" Then
strOutPut = strSqlIn & " and " & strField & "=" & strSurroundythingys & strValue & strSurroundythingys
addToWhereSql = strOutPut
Else
addToWhereSql = strSqlIn
End If

End Function

Private Sub createSql()
Dim WhereCondition As String
Dim sql As String
WhereCondition = addToWhereSql(WhereCondition, "id", txtid.Text, "'")
WhereCondition = addToWhereSql(WhereCondition, "name", txtname.Text, "'")
WhereCondition = addToWhereSql(WhereCondition, "surname", txtsurname.Text, "'")
sql = "SELECT * from table WHERE " & WhereCondition
End Sub

 

[FarligOpptreden]

This is so wrong on so many level :erm:

You may want to consider parametrising your query

In any case take a look at the ffg:

USE the COALESCE operator. I do think that there needs to some additional checking involved though.

Also check this link out on dynamic SQL

sql = "SELECT * from table
where 
id = COALESCE( " & txtID.Text & ", id ) and
name= COALESCE(" & textName.Text & " , name) and
surname = COALESCE(" & txtSurname.Text &" , surname) "

No to DSQL and coalesce in a WHERE-clause!

DSQL makes you prone to possible SQL injection if you explicitly run "EXEC @Query". Rather use sp_ExecuteSql on SQL Server if you absolutely HAVE to do DSQL. sp_ExecuteSql allows you to pass through parameters as well...

COALESCE doesn't really filter the data at all - you still have to read and touch EVERY SINGLE ROW in the result set, which effectively renders the performance improvement of filtering the data useless. COALESCE reads every record and tests the column against every value you specify for the COALESCE.

A simple solution to the OP might be:

sql = "SELECT * from table WHERE 1 = 1 "
  
if txtID.Text <> "" then
  sql = sql & " AND id = " & txtID.Text
end if

if txtName.Text <> "" then
  sql = sql & " AND name = '" & txtName.Text & "'"
end if

if txtSurname.Text <> "" then
  sql = sql & " AND surname = '" & txtSurname.Text & "'"
end if

Please note the termination of the strings with the ' characters. VERY IMPORTANT!

 

[guest2013-1]

I thought our system should be platform independent as much as possible - nope?
I get your point but I'm using SQL Server now, but they might just decide to switch to Postgres or something. I'll try to use common sql queries as much as possible.

That is what you need to decide beforehand. You can't just start writing code and go "Oh it should be platform independent" because your code most probably won't work with MySQL based on the subtle differences between SQL Server and MySQL. You're clearly writing this app in .NET, and AFAIK .NET isn't fully supported on Linux. So you most probably looking at a target market of Windows users. Now the logistical problem of getting something like SQL Express onto the machine the person is working from, or giving them the option to install it etc takes away from the "1-click" install every developer should aim for. Even though it's possible, you'll find scenarios where administrators would like to have 1 central database where people connect their applications to....so there's another question, is this a per PC application or an application to share information within the network/company? Is it an application that can work seamlessly without that central database, for example, if someone takes their laptop home and works on the data? Then you need to consider synchronizing the data and handling clashes of updates when they're connected again etc. Now how to handle the "offline" mode with the laptop not being connected, do you use MS Access/SQL Express or SQLlite as your database platform? Well it depends, if your database on the server is SQL Server, you might want to use SQL Express just for consistency because something you can do in there you can't necessarily do in MS Access or SQLlite.

See? So many questions you have to ask yourself before even beginning to write an application.

You can't be platform independent if you're writing .NET because you'll always need Windows to run it in perfectly (not talking about WINE in Linux)
You can't be platform independent re: the database because that will take tons of code and hours to perfect. I'm not sure, but if I were your boss and you spend 6 months making the applications "platform independent" then I'd fire you for wasting my time and money.

Is it really what you need?
Do you REALLY need to be platform independent?
Why choose .NET if you want to be platform independent?
Highlighting some of the questions I raised in that one long paragraph, is it really necessary/wanted to do that?

Assuming the database is per PC, nothing fancy and central like I assumed. Assuming it's only Windows PC's, your choices are MS Access, SQL Express and SQLlite.

MS Access falls away immediately in the list of possible options to use, just because if you want your application to scale to something bigger in future, it would be easier to scale a SQL Express database to a fully fledged SQL Server database and change a few connection strings (instead of an application re-write) than it would be to use MS Access. Not to mention the amount of support queries you'll save yourself from answering from people who "accidentally" deletes the MS Access db or the db getting corrupt or locked in use for some reason.

That leaves you with SQL Server and SQLlite.... is your application data intensive? Will it ever scale up? Will it be used in corporate environments where a central database would be required? Would the installation have to be small because people would need to download it or would you ship it with a CD? Is there basic information involved or are there going to be complex data and reports going to have to extracted from this program?

Depending on all of this, your choices are SQL Server Express, or SQLLite. Personally, I'd go for the latter, only because I don't know what your application is for (or the reason why you require it to be "platform independent") and think it will be perfect for a single installation on a PC with minimal resources at hand and won't require an installation of SQL Express to run the basic commands and be able to stored data. I feel it's also a better choice between MS Access & SQLLite as you won't be plagued with endless inconsistencies in SQL syntax and that stupid corruption of the MS Access db that _will_ happen (in my 10+ years in this industry I haven't worked with an MS Access database that DIDNT go corrupt which was left to me to fix... legacy systems FTL)

If you want to be platform independent, and give the user the choice in what database he/she would like to run (for whatever reason, the end user couldn't give 2 ****s what database you'd be running on really, as long as it's seemless and included in the package) then you'd be better off with a Java application capable of switching DB platforms.

Programming is 90% planning and 10% work. You'll most certainly notice this in a couple of years when you're doing 90% work in maintaining **** code/systems written (or even rewriting them) because it wasn't properly planned or thought out.

 

[James]

how much damage could someone do with Select anyway?

 

[Fuma]

I work with different dbs. So I wanted a generic statement that I can use anywhere, because i am going to use it elsewhere.
For this particular project I'm using SQL Server, but also use Oracle, MYSQL, and Postgres on other projects.
That's why I asked for an sql query, not database-specific query. We just have to agree and disagree on certain issues I guess.

 

[Roo!]

More often than not, as long as you keep to the basic SQL functions that are ansi compliant, you should be able to move from rdbms to rdbms.

You need to bare in mind though that often the db interface per rdbms is different so you will need to build a library that can handle those variations and pass back the data in a standard format for your app to use.

 

[Drake2007]

@OP: sorry for the thread hijack

Don't see why you're embaressed and doesn't take a hell of a lot of reading to simply stop the attack and move on to the next issue without worrying about trivia.

It can't get any simpler than this in PHP & MySQL so if there's a simple method in VBA please enlighten me.

$name = mysql_real_escape_string($_POST['user']);

 

[TKD01]

how much damage could someone do with Select anyway?

You can do almost anything! (depending on the security config of course)

The most popular sql injection example all over the web if you google it:
Dim sql as String = "SELECT * from table " & WhereCondition
If you add this to a textbox as input: " or 1=1' go truncate table SomeTableInMasterDB"
The or 1=1 sets the where clause to true, the " ' " ends the input and you execute a truncate statement...

Best practice:
1) Use parameters
2) When possible do NOT use inline code (like example above) - use stored procs and functions
3) Make sure your security is set correctly (most important)

Using stored procs / functions and parameters will also make the application more scalable between databases (as discussed in the thread) - albeit you'll have to make sure your DB layer is coded correctly - of course this is not always the case (between oracle and sql you should get to 95%, but talking about MS access or even opensource stuff like firebird - you will need to rework)

 

[Drake2007]

You can do almost anything! (depending on the security config of course)

The most popular sql injection example all over the web if you google it:
Dim sql as String = "SELECT * from table " & WhereCondition
If you add this to a textbox as input: " or 1=1' go truncate table SomeTableInMasterDB"
The or 1=1 sets the where clause to true, the " ' " ends the input and you execute a truncate statement...

Best practice:
1) Use parameters
2) When possible do NOT use inline code (like example above) - use stored procs and functions
3) Make sure your security is set correctly (most important)

Using stored procs / functions and parameters will also make the application more scalable between databases (as discussed in the thread) - albeit you'll have to make sure your DB layer is coded correctly - of course this is not always the case (between oracle and sql you should get to 95%, but talking about MS access or even opensource stuff like firebird - you will need to rework)

Thanks that's the answer I was looking for.
So ultimately the attacker has to guess or else brute force the table name/s anyway with no real idea of success or failure.. yes/no/not quite?

 

[TKD01]

Thanks that's the answer I was looking for.
So ultimately the attacker has to guess or else brute force the table name/s anyway with no real idea of success or failure.. yes/no/not quite?

Not quite: For example SQL uses the MASTER database to store "metadata", there is also MSDB and TEMPDB databases which holds other info (google for more information). Fiddling in the master database can screw your whole sql installation and render all db's and information in-accessible. Good example is the sys.tables or sys.objects tables.
Correct security config will protect to quite a extent (for example, setting the login account used in the application only has read-access to only the one database) - but your server is only as secure as the weakest link.

Another thing: You have to "protect" the system/server against its own users as well! Giving any type of user full access is a disaster waiting to happen (yes - we had a guy drop a live database by mistake!)

 

[guest2013-1]

(yes - we had a guy drop a live database by mistake!)

n00b

 

[Raithlin]

Thanks that's the answer I was looking for.
So ultimately the attacker has to guess or else brute force the table name/s anyway with no real idea of success or failure.. yes/no/not quite?

No - I did a course on hacking, and it's scary what you can find out based purely on a bit of creative SQL and error messages that could be returned (hint - use custom errors).

 

[Drake2007]

No - I did a course on hacking, and it's scary what you can find out based purely on a bit of creative SQL and error messages that could be returned (hint - use custom errors).

Damn ok, that gives it more serious consequences than what I initially conceived.

 

[guest2013-1]

lol, Raithlin did a course in hacking, I just **** things up naturally and wonder "why/how"

 

[Raithlin]

lol, Raithlin did a course in hacking, I just **** things up naturally and wonder "why/how"

Yeah, some people have that gift. I had to learn how.

Seriously, that was the most interesting 3 days I've ever spent in a classroom. Learning how to hack a website/program purely so we would know what to avoid/prevent. Tons of fun.