33
votes
Login form C# SQL
SQL Injection alarm
Don't use user passed text in your sql statements if you aren't using parameters.
From: https://stackoverflow.com/a/7505842/2655508
Using parameters helps prevent SQL Injection ...
- 50.1k
31
votes
Accepted
Select first value if exists, otherwise select default value
The shorter query I can think of is
SELECT COALESCE(MAX(Data), 'Default Value')
FROM Configuration
WHERE Name = 'NameOfConfiguration';
If the configuration is ...
- 478
31
votes
Accepted
Performing and timing loops with SQL queries
In the case of both if your while loops, you could easily make them for loops instead, which tends to be the best practice.
<...
- 20k
18
votes
Login form C# SQL
Cleartext passwords
Not a code-specific issue exactly but it would appear that you are storing the user's passwords in clear text. This means that anyone gaining access to the LOGIN_Tab would be ...
- 291
16
votes
Accepted
Optimize query containing too many inner join and rows
Code Review Stack Exchange advice
It really helps if you explain, in plain English, what your code does or is supposed to do. As it stands, nobody has any idea what your code is supposed to do, and ...
- 20.3k
16
votes
Accepted
Calculate amount of mugs StackExchange should give away so I'll receive one
Documentation
I think that what the query is doing is looking at your reputation rank, then rounding up to the nearest 50. It would be nice to hit "edit description" and document that for the ...
- 143k
16
votes
Accepted
Top users under age 30, sorted by age, with rank
A few things, some nitpicks, some UX, some probably-bugs. What Lyle's Mug has already stated is also part of my answer, but first things first:
UX
This query is quite unwieldy to use, because all ...
- 25.1k
16
votes
Login form C# SQL
In addition to Heslacher's answer I would also look at the using statement.
It is very important to keep a handle on resources like Sql Connections to ensure they ...
- 439
15
votes
Accepted
Searching for bad Minecraft questions on Gaming.SE
The use of the Tags field on the Post is a poor choice for the query. It relies on a table scan, which is slow, because it has to check each question. Note, you can use the ...
- 96.9k
14
votes
Winterbash 2014 "Red Shirt" Estimation
I find this pretty nicely done and really hard to pick on.
But here's a little something.
Instead of writing this condition this way:
...
- 110k
14
votes
Accepted
"How can I make this SEDE query better?" (bad title finder)
Your query is well structured, and consistent, but there is one issue you have failed to incorporate.... not all bad questions have a user. When questions are migrated, or there's a user deleted, the ...
- 96.9k
14
votes
C# function to execute queries in a SQL Server database
SqlConnection implements the IDisposable interface, and so does the SqlDataAdapter class - ...
- 74.8k
14
votes
Accepted
A wounded Zombie finder, or: how to find the almost dead undead?
Not utterly wrong, but
q.PostTypeId = 1
and
AND a.PostTypeId = 2
are superfluous. The only post types with a non-null ...
- 1,087
13
votes
Select first value if exists, otherwise select default value
The query feels tortured because SQL is designed to work with sets of data. You're using SQL like an ordinary programming language, trying to turn tables into scalars, and embedding special values in ...
- 143k
13
votes
Top users under age 30, sorted by age, with rank
If you write the query where you first order by rank and then by age, you will get a better result, and you should be able to get rid of part of the where statement, which I would think would make the ...
- 28.5k
13
votes
C# function to execute queries in a SQL Server database
There are two problems with this that are immediately apparent:
You are executing arbitrary SQL. This is a SQL Injection flaw. You'll want to use parameterized queries.
You are catching an exception ...
- 5,569
12
votes
Winterbash 2014 "Red Shirt" Estimation
-- Number of weeks must not go into the future, hence the following:
SET @weeks_ago = (CASE WHEN @weeks_ago >0 THEN -@weeks_ago ELSE @weeks_ago END);
This ...
- 74.8k
12
votes
Login form C# SQL
Security issues are covered, so just a bit on the code itself.
You have three nearly identical pieces of code, inside a switch-statement. That seems to be a code-smell. Apart from that, I'm missing a ...
- 396
11
votes
Creating ADODB Parameters on the fly
I would opt for strict type checking here. It seems a bit lazy to force it to a single when implicit in the function name. No need to use a variant and force it to a Single via a cast.
IMHO, if the ...
- 111
11
votes
Calculate amount of mugs StackExchange should give away so I'll receive one
Stylistically I'd declare variables just like in program code: Each on a single line.
Also your nested Select seems a little forced and cramped, as well as the calculation code. You maybe want to add ...
- 25.1k
11
votes
11
votes
Performing and timing loops with SQL queries
Why isn't using (SqlConnection con1 = new SqlConnection()) inside your while loop, right before ...
- 10.9k
11
votes
Searching for bad Minecraft questions on Gaming.SE
This doesn't do anything at all.
ClosedDate IS NULL
OR ClosedDate IS NOT NULL
Saying "where is or not" results in the same thing as not including the statement ...
- 30.8k
10
votes
Top users under age 30, sorted by age, with rank
It's not obvious what this query does without reading the code. You should add a title and description in Stack Exchange Data Explorer. I also find the columns illogically arranged: I would expect
...
- 143k
10
votes
Accepted
Deleting millions of rows from a MSSQL server table
The performance problem is due to the non-sargable expression YEAR(CREATEDATE). Applying a function to a column in a WHERE clause prevents efficient use of the ...
- 285
10
votes
Accepted
Query to count interactions between users
Before we begin with the code...
I just want to address one thing regarding test cases with sample data. To get the best out of a performance review of your queries, try to provide a sample that's as ...
- 20.3k
10
votes
Login form C# SQL
using as already covered
SQL injection as already covered
For getting a single row Reader is more efficient than ...
- 5,930
9
votes
Accepted
Creating an object oriented model in VBA using COM and ADODB from 2 depended SQL tables
Anytime you run SQL queries in a loop, where the number of queries scales according to the amount of data you have, performance is likely to be poor. Ideally, you should be able to fetch all the data ...
- 143k
9
votes
Accepted
Naruto, where are you?
There is not a whole lot to say, but I found a few small things that could be improved.
Consistency
...
- 20.3k
9
votes
Accepted
Sales, Taxes and Totals
Don't Repeat Yourself
Okay, a good way of getting rid of repeated formulas is to use move them into a CROSS APPLY.
We can move the following into a ...
- 1,301
Only top scored, non community-wiki answers of a minimum length are eligible