The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,499 other followers

Archive for the ‘SQL’ Category

Generating a million sequential numbers on the fly in a SQL Server query

Posted by jpluimers on 2019/01/16

A while back I wrote on Generating a million sequential numbers on the fly in a Firebird query – some solutions and speed measurements.

SQL Server has different features and performance characteristics so here are some links on doing similar things in SQL Server:

As always: if performance is important, measure before starting to optimise!

Via: [WayBack] sql – All hour of day – Stack Overflow

–jeroen

Posted in Database Development, Development, Firebird, SQL, SQL Server | Leave a Comment »

Generating a million sequential numbers on the fly in a Firebird query – some solutions and speed measurements

Posted by jpluimers on 2018/07/19

The testing was done with Firebird 2.5.x x86 on Windows 7 x64.

Where other relational database platforms have plenty of opportunities to generate sequences (see for instance the below links on Oracle and SQL Server), with Firebird you can use a WITH RECURSIVE query construct that normally is being used to manage tree structures ([WayBackPkLab – Firebird: Tree data mangement with recursive CTE).

However, that uses query stack which has a depth limit of 1024 levels. When you reach the limit, Firebird gives you an error like this:

with 
  recursive 
  sequence(n) as (        
    -- When you select more than 1024 values, this error occurs:
    -- Error while fetching data:  Too many concurrent executions of the same request    
    select 0 -- start
    from rdb$database
    union all
    select sequence.n + 1
    from sequence
    where sequence.n < 1023 -- finish
  )
select sequence.n 
from sequence
--where sequence.n in (24, 38) 
order by sequence.n

It however is a pretty quick and CU bound solution: on my system ([WayBackAMD A8-7600 @ 3.1 Ghz), it runs 1000 records within ~0.1 seconds.

In such a short time, it’s hard to see how the speed is bound, so I wanted to go for some orders of magnitude more. In ~0.1 seconds, the processor executes about 0.3 * 10^9 cycles generating 1000 numbers which is ~ 300-thousand cycles per number. That sounds like a lot of cycles for so few numbers. Would this become a better ratio for more numbers?

Read the rest of this entry »

Posted in Database Development, Development, Firebird, OracleDB, SQL, SQL Server | Leave a Comment »

SQL: “where not exists … having” formulation; anti-join alternative

Posted by jpluimers on 2018/06/26

I need to write up some notes, but there are some links that will help me:

It’s a question of readability. There is no difference in performance.
Old versions of SQL Server were silly enough to look up meta data, but not any more.

SELECT foo FROM bar WHERE EXISTS (SELECT * FROM baz WHERE baz.id = bar.id);
SELECT foo FROM bar WHERE EXISTS (SELECT 1 FROM baz WHERE baz.id = bar.id);

I am not considering NULL or “fun variants” which don’t seem intuitive to me.

SELECT foo FROM bar WHERE EXISTS (SELECT NULL FROM baz WHERE baz.id = bar.id);

SELECT foo FROM bar WHERE EXISTS (SELECT 1/0 FROM baz WHERE baz.id = bar.id);

The question popped up in comments just now. I researched the manuals of the most popular RDBMS:

A search on SO for code:"EXISTS (SELECT 1" yields 5,048 results.
A search on SO for code:"EXISTS (SELECT *" yields 5,154 results.
Updated links and counts 07.2015.

So SELECT * has the popular vote and the big commercial RDBMS on its side.
I find SELECT 1 more intuitive. It’s like saying “if at least one exists”.
Is SELECT * more intuitive?

–jeroen

 

 

Posted in Database Development, Development, Firebird, InterBase, MySQL, PostgreSQL, SQL, SQL Server | Leave a Comment »

In this day and age, people still write SQL injection vulnerable code

Posted by jpluimers on 2018/03/20

I keep being amazed that new generations of people keep writing SQL injection vulnerable code, so further below is a repeat of  [WayBack] xkcd: Exploits of a Mom on Little Bobby Tables named Robert '; Drop TABLE Students;--

Take this recent question on G+ for instance: [WayBack] Hi can you help to write correct Query for Filter 3 Data fields for Example Data1 , Data2 , Data2 txt1 = Data1 txt2= data2 txt3 = data3… – Jude De Silva – Google+ with this code fragment:

Tables:

Data1 , Data2 , Data2

Text control contents:

txt1 = Data1
txt2= data2
txt3 = data3

Examples when text property is filled:

ex1: Data1  and Data 3
ex2: Data 3 and Data2
ex3: Data 1, Data 2 Data 3

Code:

Qury.Close;
Query.Sql.Clear;
Qury.Sql.Add (Select * From Table1);
If Not (txt1.text = ' ')then
   Begin
   Qury.Sql.Add(Format ('Where Data1= ' '%s' ' ',[txt1] ));
  end;
If not (txt3.text = ' ') then
   Begin
   Qury.Sql.Add(Format ('and Data3= ' '%s' ' ',[txt1] ));
  end;

This example is wrong on so many levels, to lets explain a few:

  • use name Qury and Query for queries: are they actually two variables?
  • inconsistent keyword capitalisation for both used languages
  • incinsistent indenting and unindenting
  • mixed use of quotes for strings
  • use of space for blank fields
  • getting embedded quotes wrong

The basic solution for solving the actual problem asked is like this (assuming all user input are strings):

  • use
    • where 1=1 for a starting point for and based queries
    • where 1=0 for a starting point of or based queries
  • add a method AddAndClause or AddOrClause taking with parameters Query,  FieldName, ParameterName and ParameterValuethen when ParameterValue is not empty:
    • adds this to the SQL Text:
      • for and based queries:Format('and %s = :%s', [FieldName, ParameterName]);
      • for or based queries:Format('or %s = :%s', [FieldName, ParameterName]);
    • adds a parameter Query.ParamByName(ParameterName).AsString := ParameterValue

SQL Injection: Little Bobby Tables

Back in 2007, SQL Injection was already a very well known vulnerability (they date back to at least 1998), so Randall Munroe published [WayBack] xkcd: Exploits of a Mom on Little Bobby Tables named Robert '; Drop TABLE Students;--


School: “Hi, this is your son’s school. We’re having some computer trouble.”
Mom: “Oh, dear — Did he break something?”
School: “In a way. Did you really name your son Robert'); DROP TABLE Students;-- ?
Mom: “Oh. Yes. Little Bobby Tables we call him.”
School: “Well, we’ve lost this year’s student records. I hope you’re happy.”
Mom: “And I hope you’ve learned to sanitize your database inputs.”
(Alt-text: “Her daughter is named Help I’m trapped in a driver’s license factory.”)

It did not just get explained at [WayBack] 327: Exploits of a Mom – explain xkcd (Explain xkcd is a wiki dedicated to explaining the webcomic xkcd. Go figure.), Little Bobby Tables got his own page there: [WayBack] Little Bobby Tables – explain xkcd.

Like people continuing writing SQL injection vulnerable code, XKCD posted another SQL injection in [WayBack] 1253: Exoplanet Names – explain xkcd by using e'); DROP TABLE PLANETS;-- as name for Planet e of Star Gliese 667.

Preventing SQL Injection

A few years later, around 2009, Bobby Tables inspired [WayBack] bobby-tables.com: A guide to preventing SQL injection explaining:

  • what not to do “Don’t try to escape invalid characters. Don’t try to do it yourself.”
  • what do to: “Learn how to use parameterized statements. Always, every single time.”
bobby-tables.com

bobby-tables.com

It goes on with many examples of parameterised queries in many environments and language, for instance in the language used above: Delphi.

You can contribute new environments and languages as the site has source code at [WayBack] GitHub – petdance/bobby-tables: bobby-tables.com, the site for preventing SQL injections.

Finally, it points to a few more resources:

WayBack bobby-tables.com: A guide to preventing SQL injection in Delphi

Delphi

To use a prepared statement, do something like this:

query.SQL.Text := 'update people set name=:Name where id=:ID';
query.Prepare;
query.ParamByName( 'Name' ).AsString := name;
query.ParamByName( 'ID' ).AsInteger := id;
query.ExecSQL;

–jeroen

Read the rest of this entry »

Posted in Development, Software Development, SQL | Leave a Comment »

How to terminate sqlcmd immediately after execution completed? – Stack Overflow

Posted by jpluimers on 2018/01/24

The subtle difference between -q and -Q: the latter will exit after executing the command (regardless of the SQL server version; I think this was introduced in SQL Server 2005 or 2000).

Inside the command, you can use single ' quotes for strings.

C:\Users\jeroenp>sqlcmd /?
Microsoft (R) SQL Server Command Line Tool
Version 10.50.2500.0 NT x64
Copyright (c) Microsoft Corporation.  All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f  | i:[,o:]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

–jeroen

via: [WayBackc# – How to terminate sqlcmd immediately after execution completed? – Stack Overflow

Posted in Database Development, Development, Software Development, SQL, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | 1 Comment »

 
%d bloggers like this: