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,380 other followers

Archive for the ‘Database Development’ Category

Firebird error “Operating system call _beginthreadex failed. Error code 8” can mean your server process ran out of memory

Posted by jpluimers on 2018/06/14

On a production system we had this error occurring without warning:

Operating system call _beginthreadex failed. Error code 8

The cause was running out of private bytes or virtual size as they were almost 2 gigabyte which is too much for a 32-bit process:

The not so nice thing is that there were no memory warnings in the Firebird.log file at all.

In the client application the DAC (Data Access Layer) was getting lots of  “unable to allocate memory from operating system” errors back from Firebird (and logging them in the client log file), so the client didn’t run out of memory: the server did.

This was with 2.5.2.26540 (at that time the most recent version) and it looks like more people suffer from this:

We put a monitor to watch the fbserver.exe process and warn us if it was reaching 1.5 gigabyte so we could re-start it before running out of memory.

–jeroen

Posted in Database Development, Development, Firebird, Power User | 2 Comments »

CVE-2017-11509: Firebird fbudf Module Authenticated Remote Code Execution – Firebird News

Posted by jpluimers on 2018/05/31

Ouch (despite one needs authenticated access): [WayBack] Firebird fbudf Module Authenticated Remote Code Execution – Firebird News

Here is the description for CVE-2017-11509

An authenticated remote attacker can execute arbitrary code in Firebird SQL
Server versions 2.5.7 and 3.0.2 by executing a malformed SQL statement. The
only known solution is to disable external UDF libraries from being loaded. In
order to achieve this, the default configuration has changed to UdfAccess=None.

This will prevent the fbudf module from being loaded, but may also break other
functionality relying on modules.

Here is the Debian security page with the issue : CVE-2017-11509

The thing I am really not happy about is that the 90 day limit has been overdrawn by about 180 days (see https://www.tenable.com/security/research/tra-2017-36)

Related:

Via:

–jeroen

Posted in Database Development, Development, Security, Software Development | Leave a Comment »

GDPR for database professionals and DBAs. Poster.

Posted by jpluimers on 2018/05/17

A poster showing how to prepare your Oracle database for GDPR. Wait for the email download link by visiting GDPR for database professionals and DBAs. Poster (they mention to be GDPR compliant in [WayBack] their policy), or look at the WayBack machine to download it now.

One takeaway for me: you also need to scrub backups when removing someone your records.

Via: [WayBack] sqldev.tech/gdpr_poster – Michael Thuma – Google+

–jeroen

Posted in Communications Development, Database Development, Design Patterns, Development, Software Development | 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 »

Does this company still exist today? ; DROP TABLE “COMPANIES”;– LTD

Posted by jpluimers on 2018/02/01

About a year ago, this company was incorporated: https://beta.companieshouse.gov.uk/company/10542519

; DROP TABLE “COMPANIES”;– LTD

[WayBack; DROP TABLE “COMPANIES”;– LTD – Overview (free company information from Companies House)

via: [WayBack] From the Trololo-Dept: https://beta.companieshouse.gov.uk/company/10542519 – Kristian Köhntopp – Google+

–jeroen

Posted in Database Development, Development, Power User, Security | Leave a Comment »

 
%d bloggers like this: