SQL Injection (تزریق به دیتابیس):

 تزریق به پایگاه داده یا دیتابیس (SQL Injection) نوعی از حملات وب است که در آن فرد حمله کننده یا هکر می‌تواند اقدام به اجرا کردن دستورات دلخواه و مخرب خود بر روی پایگاه داده وب سایت مورد هدف کند. در این حمله، حمله کننده با استفاده از دانش خود (یا تنها با استفاده از یک برنامه ساده!) می‌تواند از نقض‌های امنیتی موجود در کدهای نوشته شده توسط برنامه نویس سایت استفاده کرده و به اصطلاح آن‌ها را اکسپلویت کند. چون در این حمله هکر درواقع به کد اسکیوال، کد دلخواه خود را اضافه می‌کند، تزریق SQL نام گرفته است.

یک حمله موفق اس کیو ال اینجکشن می‌تواند به راحتی سبب افشای داده‌های مهم در دیتابیس (ازجمله رمزهای عبور، اطلاعات فردی کاربران و ...)، اضافه کردن داده‌های دلخواه حمله کننده به دیتابیس یا حذف کردن داده‌های خاص از دیتابیس گردد.

حمله SQL Injection چگونه کار می‌کند؟

فرض کنید شما یک برنامه نویس سمت وب هستید و قصد دارید با دیتابیس از طریق دستورات SQL ارتباط برقرار کنید. چون همانطور که می‌دانید استفاده از زبان SQL برای برقراری ارتباط با دیتابیس ضروری است. کد زیر را فرض کنید که در زبان PHP نوشته شده است:

$sql_statement = "SELECT ‌* FROM users WHERE name = '" . $_GET["name"] . "'";

دستور بالا مقدار فیلد یا همان پارامتر username که توسط متد GET، متدی که پارامترها در مسیر URL مشخص می‌شوند، از طرف کاربر به سرور ارسال شده را در بین دو علامت ' قرار می‌دهد (تا توسط سرور SQL به عنوان رشته حروفی شناخته شود) و پس از آن فرض کنید که مقدار موجود در متغیر $sql_statement قرار است اجرا شود.

فرض کنید که کاربر فیلد name را به صورت عادی مثل مقدار زیر پر کند. مسیر از طریق متد GET به این شکل خواهد شد:

http://example.com/vul.php?name=Amirreza

در این صورت Query  موجود در متغیر sql_statement به این صورت خواهد بود:

$sql_statement = "SELECT * FROM users WHERE name = 'Amirreza'";

و کوئری (حرف همزه به دلیل فونت سایت مانند ن نشان داده می‌شود) بدون مشکل اجرا خواهد شد. در دیتابیس فیلد name با رشته Amirreza مقایسه خواهد شد و در جایی که نتیجه یافت شد، همه فیلدها (*) برای بقیه کدها در دسترس خواهد بود.

حال فرض کنید که کاربر (در اینجا، حمله کننده) فیلد name را به صورت زیر پر کند:

http://example.com/vul.php?name=' UNION UPDATE users SET email = '[e-mail]' WHERE name = 'admin'

به یک نام کاربری شبیه نیست، درست است؟ خب حال این مقدار را جایگزین پارامتر username متد POST می‌کنیم. متغیر کوئری ما به این شکل خواهد بود:

$sql_statement = "SELECT ‌* FROM users WHERE name = '' UNION UPDATE users SET email = '[e-mail]' WHERE name = 'admin'";

خب کوئری بالا هم اجرا خواهد شد ولی با این تفاوت که در واقع هکر کوئری دلخواه خود را اجرا کرده است! در دیتابیس فیلد name با رشته خالی جستجو خواهد شد ولی چیزی پیدا نخواهد شد، تا اینجا برای هکر مهم نیست. اما هکر با استفاده از دستور UNION، کوئری دوم خود را نیز اجرا می‌کند و در کوئری دوم، ایمیل کاربر admin (که به احتمال زیاد تمامی دسترسی‌های موجود را دارد) را به ایمیل دلخواه تغییر (UPDATE) می‌دهد.

چگونه از باگ SQL Injection جلوگیری کنیم؟

نحوه جلوگیری از باگ SQL Injection نیاز به آگاهی برنامه نویس تحت وب از نحوه عملکرد این باگ دارد و جلوگیری از آن بسیار ساده تر از چیزی است که معمولاً تصور می‌شود.

 

نحوه جلوگیری از باگ SQL Injection:

کوئری‌های پارامتر بندی شده نوعی ارتباط با دیتابیس است که در آن کوئری (Query) ما یک بار بدون داشتن مقدار متغیر، به سرور دیتابیس ارسال شده و بعد از آن، متغیرها یکی یکی و به صورت جداگانه ارسال می‌شوند. این قابلیت مزایای مختلفی دارد از جمله:

§         افزایش سرعت کار با دیتابیس: دستور SQL خود را یک بار معرفی کرده و به سرور ارسال می‌کنیم و بعد از آن هر چند دفعه که نیاز است، فقط متغیرها را ارسال می‌کنیم. با این کار هم تاخیر آماده کردن کوئری کامل کمتر می‌شود و هم پهنای باند استفاده شده چون کوئری تنها یک بار ارسال می‌شود، بسیار بهینه تر خواهد بود.

§         افزایش امنیت: متغیرهای ما به صورت مستقیم در دستور sql مان قرار نگرفته و به صورت جداگانه ارسال می‌شود. بنابراین هکر قادر نخواهد بود به استیتمنت (همان کوئری) ما دستور دیگری اضافه کند و بدین ترتیب از باگ SQL Injection جلوگیری می‌شود.

 

در زبان PHP:

اگر از زبان پی اچ پی استفاده می‌کنید، یکی از کاربردی ترین روش‌ها برای پارامتری کردن کوئری‌ها استفاده از اکستنشن PDO (مخفف PHP Data Objects) است. با استفاده از این کلاس می‌توانید به اکثر دیتابیس‌ها بدون هیچ مشکل وصل شوید. البته از روش‌های دیگر (مثل MySQL) نیز می‌توانید برای پارامتری کردن استفاده کنید ولی پیشنهاد می‌کنم PDO را انتخاب کنید چون علاوه بر سادگی، از دیتابیس‌های زیادی پشتیبانی می‌کند.

ابتدا متغیرهای خود را تعریف می‌کنیم:

// نام کاربری یوزر دیتابیس

$username = "root";

// پسورد یوزر دیتابیس

$password = "";

// هاست دیتابیس

$host = "localhost";

// نام دیتابیس

$dbname = "my_database";

حال نوبت به تعریف آبجکت PDO رسیده. این کار را در بلوک Try - Catch انجام می‌دهیم تا در صورت وجود مشکل، ما را مطلع کند:

try {
// آبجکت پی دی او خود را در زیر تعریف می‌کنیم
$db = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password, $options);
} catch(PDOException $ex) {
// اگر مشکلی در ارتباط با دیتابیس پیش آمد
die("Failed to connect to the database: " . $ex->getMessage());
}

همانطور که می‌بینید، در $db آبجکت PDO ما با DSN ای که داخل آن نوشته شده، تعریف شده است.

در این DSN از charset=utf8 استفاده کرده‌ایم. با این خاصیت به دیتابیس خواهیم فهماند که فقط کاراکترهایی با اینکدینگ یا کاراکتر ست UTF-8 را قبول کند. این مورد در جلوگیری از SQL Injection  بسیار کاربردی است زیرا کاراکترهایی خارج از UTF-8 در سمت دیتابیس قبول نخواهند شد.

نکته: اگر PDO شما از charset در DSN خود پشتیبانی نکند، باید کاراکتر ست را به صورت دستی ست کنید. در این حالت، کافیست قبل از بلوک Try - Catch دستور زیر را بنویسید که آرایه‌ای از تنظیمات اختیاری برای PDO می‌سازیم که می‌تواند شامل بیش از یک تنظیم باشد. در این آرایه، با دستور SET NAMES utf8 کاراکتر ست را انتخاب می‌کنیم:

$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');

توجه کنید که تنها اگر از charset پشتیبانی نکند از دستور بالا استفاده کنید!

خب حال برخی از خواص $db خود که شامل یک آبجکت PDO است را مشخص می‌کنیم. ابتدا حالت نشان دادن خطا را فعال می‌کنیم تا در قسمتی از کدها اگر مشکلی بود، بتوانیم در بلوک Try - Catch آن را مدیریت کنیم:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

توجه کنید که پس از اتمام برنامه نویسی وب، شدیداً توصیه می‌شود که حالت نشان دادن خطا را غیرفعال کنید تا هکرهای تازه وارد از وجود باگ (اگر باشه) مطلع نشوند.

خب حال خصوصیت نحوه بازگرداندن داده‌ها را انتخاب می‌کنیم. در این قسمت تعریف می‌کنیم که داده‌ها را به صورت آرایه‌ای با نام ستون تیبل دیتابیس نشان دهد یعنی مثلاً اگر ستونی با نام user_lastname را درخواست کنیم، آن را می‌توانیم در آرایه‌ای با همین نام دریافت کنیم:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

خب حال به مهمترین خصوصیت PDO رسیده‌ایم. ممکن است فرض کنید که PDO همیشه داده‌ها را به صورت پارامتر شده به دیتابیس ارسال می‌کند اما این تصور اشتباه است، ممکن است از طرف دیتابیس این قابلیت تایید نشود بنابراین PDO کوئری ما را به صورت از پیش آماده شده ارسال خواهد کرد که باعث بوجود آمدن باگ SQL Injection خواهد شد. ما با ست کردن خصوصیت زیر به PDO می‌گوییم که همیشه از عمل پارامتر کردن استفاده کند:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

با این حال باز هم امکان این وجود دارد که PDO در برخی از دیتابیس‌ها، از حالت پارامتر شده استفاده نکند. تنها مزیت MySQLi نسبت به PDO این است که در MySQLi همیشه و همیشه کوئری‌ها بصورت پارامتر شده ارسال می‌شوند. با این حال PDO مزیت‌های بیشتری نسبت به MySQLi دارد و پارامتری کردن آن با دستور بالا و استفاده از نسخه دیتابیس هماهنگ، تضمین خواهد شد.

 

خب حال که PDO خود را تنظیم و آماده استفاده کردیم، بد نیست یک مثال از نحوه کار با PDO بنویسیم.

ابتدا از ترای - کچ برای مدیریت خطاها استفاده می‌کنیم:

try {
//کوئری خود را ابتدا به دیتابیس ارسال می‌کنیم
$stmt = $db->prepare("SELECT name, lastname FROM users WHERE key = :getkey AND username = :getusername);

//پارامترهای دلخواه خود را بایند می‌کنیم یا می‌توانیم به صورت مستقیم در اجرا، آن‌ها را وارد کنیم
$stmt->bindParam(':getkey', $key, PDO::PARAM_INT); // با ست کردن آرگامنت سوم، نوع داده را مشخص می‌کنیم. در اینجا، عدد صحیح
$stmt->bindParam(':getusername', $_POST[""]); //اگر نوع را مشخص نکنیم، به صورت پیشفرض رشته حروفی است

//حال کوئری و پارامترها را جداگانه به سرور می‌فرستیم
$stmt->execute();

//داده‌های گرفته شده از دیتابیس را ذخیره می‌کنیم
$rowF = $stmt->fetchAll();

if ($rowF){
// اگر رکوردی پیدا شد

foreach ($rowF as $row){
//برای هر رکورد، عملی را انجام می‌دهیم
echo "Your name is: " . $row["name"] . " and your last name is: " . $row["lastname"];
}
}else{
// اگر رکوردی پیدا نشد
echo "No records found with the Key and Username.";
}
}catch(PDOException $e){
// اگر خطایی بوجود آمد. پس از اتمام برنامه نویسی حتماً این خطا را از دید کاربر مخفی کنید
echo "ERROR: " . $query . "<br>" . $e->getMessage();
}

امنیت اضافی

§         نسخه‌های مختلف دیتابیس‌ها مثلاً MySQL های قدیمی دارای مشکلات امنیتی مختلفی هستند.

§         همیشه برنامه دیتابیس خود به روز نگه دارید.

§         همچنین زبان‌های برنامه نویسی PHP و ASP.NET پچ‌های امنیتی برای باگ‌های خود منتشر می‌کنند، پس همیشه از آخرین نسخه استفاده کنید.

§         پس از اتمام کار برنامه نویسی، خاصیت نشان دادن خطاهای دیتابیس را غیرفعال کنید یا حداقل آن‌ها را به کاربر نشان ندهید.

§         یوزری که از آن برای دسترسی به دیتابیس استفاده میکنید را محدود کنید!

§         اطلاعات حساس مقایساتی (مانند رمزهای عبور) را به صورت هش شده با الگوریتمهای قوی، سالت (Salt) شده و کند ذخیره کنید تا حتی درصورت از دست رفتن، پیدا کردنشان دشوار باشد.

 

 

 

 

 

 

Abusing XML and JSON

First off

JSON is just XML with less features and less syntax

Now, everyone knows that XML is awesome. The corollary is thus:

JSON is less awesome

Don’t use JSON.

Now that we’ve settled this, we can safely ignore the ongoing JSON-in-the-database-hype (which most of you will regret in five years anyway), and move on to the final example. How to do XML in the database.

This is what we want to do:

Given the original XML document, we want to parse that document, unnest the comma-separated list of films per actor, and produce a denormalised representation of actors/films in a single relation.

Ready. Set. Go. This is the idea. We have three CTE:

1 2 3 4 5 6 7 8 9 10 11

WITH RECURSIVE   x(v) AS (SELECT '...'::xml),   actors(     actor_id, first_name, last_name, films   ) AS (...),   films(     actor_id, first_name, last_name,     film_id, film   ) AS (...) SELECT * FROM films

In the first one, we simply parse the XML. Here with PostgreSQL:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

WITH RECURSIVE   x(v) AS (SELECT ' <actors>   <actor>     <first-name>Bud</first-name>     <last-name>Spencer</last-name>     <films>God Forgives... I Don’t, Double Trouble, They Call Him Bulldozer</films>   </actor>   <actor>     <first-name>Terence</first-name>     <last-name>Hill</last-name>     <films>God Forgives... I Don’t, Double Trouble, Lucky Luke</films>   </actor> </actors>'::xml),   actors(actor_id, first_name, last_name, films) AS (...),   films(actor_id, first_name, last_name, film_id, film) AS (...) SELECT * FROM films

Easy.

Then, we do some XPath magic to extract the individual values from the XML structure and put those into columns:

1 2 3 4 5 6 7 8 9 10 11 12 13

WITH RECURSIVE   x(v) AS (SELECT '...'::xml),   actors(actor_id, first_name, last_name, films) AS (     SELECT       row_number() OVER (),       (xpath('//first-name/text()', t.v))[1]::TEXT,       (xpath('//last-name/text()' , t.v))[1]::TEXT,       (xpath('//films/text()'     , t.v))[1]::TEXT     FROM unnest(xpath('//actor', (SELECT v FROM x))) t(v)   ),   films(actor_id, first_name, last_name, film_id, film) AS (...) SELECT * FROM films

Still easy.

Finally, just a bit of recursive regular expression pattern matching magic, and we’re done!

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

WITH RECURSIVE   x(v) AS (SELECT '...'::xml),   actors(actor_id, first_name, last_name, films) AS (...),   films(actor_id, first_name, last_name, film_id, film) AS (     SELECT actor_id, first_name, last_name, 1,       regexp_replace(films, ',.+', '')     FROM actors     UNION ALL     SELECT actor_id, a.first_name, a.last_name, f.film_id + 1,       regexp_replace(a.films, '.*' || f.film || ', ?(.*?)(,.+)?', '\1')     FROM films AS f     JOIN actors AS a USING (actor_id)     WHERE a.films NOT LIKE '%' || f.film   ) SELECT * FROM films

Let’s conclude:

Capping a Running Total

So far, we’ve seen how to calculate an “ordinary” running total with SQL using window functions. That was easy. Now, how about if we cap the running total such that it never goes below zero? Essentially, we want to calculate this:

| DATE       | AMOUNT | TOTAL |
|------------|--------|-------|
| 2012-01-01 |    800 |   800 |
| 2012-02-01 |   1900 |  2700 |
| 2012-03-01 |   1750 |  4450 |
| 2012-04-01 | -20000 |     0 |
| 2012-05-01 |    900 |   900 |
| 2012-06-01 |   3900 |  4800 |
| 2012-07-01 |  -2600 |  2200 |
| 2012-08-01 |  -2600 |     0 |
| 2012-09-01 |   2100 |  2100 |
| 2012-10-01 |  -2400 |     0 |
| 2012-11-01 |   1100 |  1100 |
| 2012-12-01 |   1300 |  2400 |

So, when that big negative AMOUNT -20000 was subtracted, instead of displaying the real TOTAL of -15550, we simply display 0. In other words (or data sets):

| DATE       | AMOUNT | TOTAL |
|------------|--------|-------|
| 2012-01-01 |    800 |   800 | GREATEST(0,    800)
| 2012-02-01 |   1900 |  2700 | GREATEST(0,   2700)
| 2012-03-01 |   1750 |  4450 | GREATEST(0,   4450)
| 2012-04-01 | -20000 |     0 | GREATEST(0, -15550)
| 2012-05-01 |    900 |   900 | GREATEST(0,    900)
| 2012-06-01 |   3900 |  4800 | GREATEST(0,   4800)
| 2012-07-01 |  -2600 |  2200 | GREATEST(0,   2200)
| 2012-08-01 |  -2600 |     0 | GREATEST(0,   -400)
| 2012-09-01 |   2100 |  2100 | GREATEST(0,   2100)
| 2012-10-01 |  -2400 |     0 | GREATEST(0,   -300)
| 2012-11-01 |   1100 |  1100 | GREATEST(0,   1100)
| 2012-12-01 |   1300 |  2400 | GREATEST(0,   2400)

How will we do it?

Exactly. With obscure, vendor-specific SQL. In this case, we’re using Oracle SQL

How does it work? Surprisingly easy!

Just add MODEL after any table, and you’re opening up a can of awesome SQL worms!

1 2 3 4

SELECT ... FROM some_table   -- Put this after any table MODEL ...

Once we put MODEL there, we can implement spreadsheet logic directly in our SQL statements, just as with Microsoft Excel.

The following three clauses are the most useful and widely used (i.e. 1-2 per year by anyone on this planet):

1 2 3 4 5 6 7 8 9

MODEL   -- The spreadsheet dimensions   DIMENSION BY ...       -- The spreadsheet cell type   MEASURES ...       -- The spreadsheet formulas   RULES ...

The meaning of each of these three additional clauses is best explained with slides again.

The DIMENSION BY clause specifies the dimensions of your spreadsheet. Unlike in MS Excel, you can have any number of dimensions in Oracle:

The MEASURES clause specifies the values that are available in each cell of your spreadsheet. Unlike in MS Excel, you can have a whole tuple in each cell in Oracle, not just a single value.

The RULES clause specifies the formulas that apply to each cell in your spreadsheet. Unlike in MS Excel, these rules / formulas are centralised at a single place, instead of being put inside of each cell:

This design makes MODEL a bit harder to use than MS Excel, but much more powerful, if you dare. The whole query will then be “trivially”:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT * FROM (   SELECT date, amount, 0 AS total   FROM amounts ) MODEL   DIMENSION BY (row_number() OVER (ORDER BY date) AS rn)   MEASURES (date, amount, total)   RULES (     total[any] = greatest(0,     coalesce(total[cv(rn) - 1], 0) + amount[cv(rn)])   )

This whole thing is so powerful, it ships with its own whitepaper by Oracle, so rather than explaining things further here in this article, please do read the excellent whitepaper:

http://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf

The subset sum problem with SQL

This is my favourite!

What is the subset sum problem? Find a fun explanation here:
https://xkcd.com/287

And a boring one here:
https://en.wikipedia.org/wiki/Subset_sum_problem

Essentially, for each of these totals…

| ID | TOTAL |
|----|-------|
|  1 | 25150 |
|  2 | 19800 |
|  3 | 27511 |

… we want to find the “best” (i.e. the closest) sum possible, consisting of any combination of these items:

| ID   |  ITEM |
|------|-------|
|    1 |  7120 |
|    2 |  8150 |
|    3 |  8255 |
|    4 |  9051 |
|    5 |  1220 |
|    6 | 12515 |
|    7 | 13555 |
|    8 |  5221 |
|    9 |   812 |
|   10 |  6562 |

As you’re all quick with your mental mathemagic processing, you have immediately calculated these to be the best sums:

| TOTAL |  BEST | CALCULATION
|-------|-------|--------------------------------
| 25150 | 25133 | 7120 + 8150 + 9051 + 812
| 19800 | 19768 | 1220 + 12515 + 5221 + 812
| 27511 | 27488 | 8150 + 8255 + 9051 + 1220 + 812

How to do it with SQL? Easy. Just create a CTE that contains all the 2n*possible* sums and then find the closest one for each TOTAL:

1 2 3 4 5 6 7 8 9

-- All the possible 2N sums WITH sums(sum, max_id, calc) AS (...)   -- Find the best sum per “TOTAL” SELECT   totals.total,   something_something(total - sum) AS best,   something_something(total - sum) AS calc FROM draw_the_rest_of_the_*bleep*_owl

As you’re reading this, you might be like my friend here:

But don’t worry, the solution is – again – not all that hard (although it doesn’t perform due to the nature of the algorithm):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

WITH sums(sum, id, calc) AS (   SELECT item, id, to_char(item) FROM items   UNION ALL   SELECT item + sum, items.id, calc || ' + ' || item   FROM sums JOIN items ON sums.id < items.id ) SELECT   totals.id,   totals.total,   min (sum) KEEP (     DENSE_RANK FIRST ORDER BY abs(total - sum)   ) AS best,   min (calc) KEEP (     DENSE_RANK FIRST ORDER BY abs(total - sum)   ) AS calc, FROM totals CROSS JOIN sums GROUP BY totals.id, totals.total

In this article, I won’t explain the details of this solution, because the example has been taken from a previous article that you can find here:

How to Find the Closest Subset Sum with SQL

Enjoy reading the details, but be sure to come back here for the remaining 4 tricks:

Finding the Largest Series with no Gaps

Stack Overflow has this very nice feature to motivate people to stay on their website for as long as possible. Badges:

For scale, you can see how many badges I have. Tons.

How do you calculate these badges? Let’s have a look at the “Enthusiast” and the “Fanatic”. These badges are awarded to anyone who spends a given amount of consecutive days on their platform. Regardless of any wedding date or wife’s birthday, you HAVE TO LOG IN, or the counter starts from zero again.

Now as we’re doing declarative programming, we don’t care about maintaining any state and in-memory counters. We want to express this in the form of online analytic SQL. I.e. consider this data:

| LOGIN_TIME          |
|---------------------|
| 2014-03-18 05:37:13 |
| 2014-03-16 08:31:47 |
| 2014-03-16 06:11:17 |
| 2014-03-16 05:59:33 |
| 2014-03-15 11:17:28 |
| 2014-03-15 10:00:11 |
| 2014-03-15 07:45:27 |
| 2014-03-15 07:42:19 |
| 2014-03-14 09:38:12 |

That doesn’t help much. Let’s remove the hours from the timestamp. That’s easy:

1 2 3 4

SELECT DISTINCT   cast(login_time AS DATE) AS login_date FROM logins WHERE user_id = :user_id

Which yields:

| LOGIN_DATE |
|------------|
| 2014-03-18 |
| 2014-03-16 |
| 2014-03-15 |
| 2014-03-14 |

Now, that we’ve learned about window functions, let’s just add a simple row number to each of these dates:

1 2 3 4

SELECT   login_date,   row_number() OVER (ORDER BY login_date) FROM login_dates Which produces: | LOGIN_DATE | RN | |------------|----| | 2014-03-18 | 4 | | 2014-03-16 | 3 | | 2014-03-15 | 2 | | 2014-03-14 | 1 |

Still easy. Now, what happens, if instead of selecting these values separately, we subtract them?

1 2 3 4

SELECT   login_date -   row_number() OVER (ORDER BY login_date) FROM login_dates

We’re getting something like this:

| LOGIN_DATE | RN | GRP        |
|------------|----|------------|
| 2014-03-18 |  4 | 2014-03-14 |
| 2014-03-16 |  3 | 2014-03-13 |
| 2014-03-15 |  2 | 2014-03-13 |
| 2014-03-14 |  1 | 2014-03-13 |

Wow. Interesting. So, 14 - 1 = 13, 15 - 2 = 13, 16 - 3 = 13, but 18 - 4 = 14. 

There’s a simple example for this behaviour:

1. ROW_NUMBER() never has gaps. That’s how it’s defined
2. Our data, however, does

So when we subtract a “gapless” series of consecutive integers from a “gapful” series of non-consecutive dates, we will get the same date for each “gapless” subseries of consecutive dates, and we’ll get a new date again where the date series had gaps.

Huh.

This means we can now simply GROUP BY this arbitrary date value:

1 2 3 4 5 6 7

SELECT   min(login_date), max(login_date),   max(login_date) -   min(login_date) + 1 AS length FROM login_date_groups GROUP BY grp ORDER BY length DESC

And we’re done. The largest series of consecutive dates with no gaps has been found:

| MIN        | MAX        | LENGTH |
|------------|------------|--------|
| 2014-03-14 | 2014-03-16 |      3 |
| 2014-03-18 | 2014-03-18 |      1 |

With the full query being:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

WITH   login_dates AS (     SELECT DISTINCT cast(login_time AS DATE) login_date     FROM logins WHERE user_id = :user_id   ),   login_date_groups AS (     SELECT       login_date,       login_date - row_number() OVER (ORDER BY login_date) AS grp     FROM login_dates   ) SELECT   min(login_date), max(login_date),   max(login_date) - min(login_date) + 1 AS length FROM login_date_groups GROUP BY grp ORDER BY length DESC

SQL Server Profiler

ابزاریست برای مشاهده عملیاتی که بر روی اس کیو ال انجام می شود.
از منو Tools گزینه Sql Server Profiler را انتخاب کنید ، به اس کیو ال کانکت بشید ، در پنجره باز شده گزینه Run رو بزنید ، حالا یک دستوری بر روی دیتابیس اجرا کنید ، (مثلا یک پرسجو)
مشاهده میکنید که در اس کیو ال پروفایلر همون دستور را نمایش می دهد با یک سری از مشخصات دیگه از جمله زمان اجرا ، سیستم اجرا کننده ، کاربر اجرا کننده و ...
در این ابزار میشود هر نوع فیلتری رو انجام داد ، به عنوان مثال بگیم اطلاعاتی که یک سیستم خاص بر روی یک دیتابیس خاص انجام میدهد رو به ما نمایش بدهد و ....

Everything is a Table

This is the most trivial of tricks, and not even really a trick, but it is fundamental to a thorough understanding of SQL: Everything is a table! When you see a SQL statement like this:

SELECT *
FROM person

… you will quickly spot the table person sitting right there in the FROM clause. That’s cool, that is a table. But did you realise that the whole statement is also a table? For instance, you can write:

SELECT *
FROM (
  SELECT *
  FROM person
) t

And now, you have created what is called a “derived table” – i.e. a nested SELECT statement in a FROM clause.

That’s trivial, but if you think of it, quite elegant. You can also create ad-hoc, in-memory tables with the VALUES() constructor as such, in some databases (e.g. PostgreSQL, SQL Server):

SELECT *
FROM (
  VALUES(1),(2),(3)
) t(a)

Which simply yields:

 a
---
 1
 2
 3

If that clause is not supported, you can revert to derived tables, e.g. in Oracle:

SELECT *
FROM (
  SELECT 1 AS a FROM DUAL UNION ALL
  SELECT 2 AS a FROM DUAL UNION ALL
  SELECT 3 AS a FROM DUAL
) t

Now that you’re seeing that VALUES() and derived tables are really the same thing, conceptually, let’s review the INSERT statement, which comes in two flavours:

-- SQL Server, PostgreSQL, some others:
INSERT INTO my_table(a)
VALUES(1),(2),(3);

-- Oracle, many others:
INSERT INTO my_table(a)
SELECT 1 AS a FROM DUAL UNION ALL
SELECT 2 AS a FROM DUAL UNION ALL
SELECT 3 AS a FROM DUAL

In SQL everything is a table. When you’re inserting rows into a table, you’re not really inserting individual rows. You’re really inserting entire tables. Most people just happen to insert a single-row-table most of the time, and thus don’t realise what INSERT really does.

Everything is a table. In PostgreSQL, even functions are tables:

SELECT *
FROM substring('abcde', 2, 3)

The above yields:

substring
---------
bcd

If you’re programming in Java, you can use the analogy of the Java 8 Stream API to take this one step further. Consider the following equivalent concepts:

TABLE          : Stream<Tuple<..>>
SELECT         : map() 
DISTINCT       : distinct()
JOIN           : flatMap()
WHERE / HAVING : filter()
GROUP BY       : collect()
ORDER BY       : sorted()
UNION ALL      : concat()

With Java 8, “everything is a Stream” (as soon as you start working with Streams, at least). No matter how you transform a stream, e.g. with map() or filter(), the resulting type is always a Stream again.

We’ve written an entire article to explain this more deeply, and to compare the Stream API with SQL:
Common SQL Clauses and Their Equivalents in Java 8 Streams

And if you’re looking for “better streams” (i.e. streams with even more SQL semantics), do check out jOOλ, an open source library that brings SQL window functions to Java.