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.

secure and attacks in DB

امنیت درپایگاه داده ها فهرست مطالب مقدمه تعریف داده روشهای ذخیره داده ها
عنوان: حملات و امنیت
حجم: 966 کیلوبایت

منگو دی بی

MongoDB را می‌توان یکی از پرمخاطب‌ترین پایگاه‌های داده‌ موجود در جمع اعضای خانواده NoSQL‌ دانست که بنابر برخی آمارهای جمع‌آوری شده، لقب پرمخاطب‌ترین را نیز به‌خود اختصاص داده است. این پایگاه داده‌ یک مدل منعطف، پویا و سندگرا را ارائه می‌کند که ساختاری با خروجی بسیار بالا و قابلیت مقیاس‌پذیری آسان را دارا است که در پروژه‌ها و سیستم‌های کلان داده یا همان Big Data به‌شدت مورد نیاز است. این پایگاه ‌داده‌ به‌دلایل مختلفی از جمله مواردی که گفته شد، در سال‌های گذشته مورد استقبال فراوانی قرار گرفته و راه‌اندازها و کتابخانه‌های توسعه‌داده شده برای آن برای اغلب زبان‌های برنامه‌نویسی، از جمله زبان محبوب #C در دسترس قرار گرفته است. در این مقاله سعی خواهیم کرد تا ضمن بررسی مشخصات MongoDB به چگونگی نصب و به‌کارگیری این پایگاه داده پرداخته و در آینده به نحوه دستیابی به آن از طریق زبان برنامه‌نویسی #C بپردازیم.

نصب MongoDB در ویندوز

پایگاه داده‌ MongoDB را می‌توان جزء آن‌دسته از پایگاه‌های داده‌ای دانست که اغلب پلتفرم‌های مطرح دنیا را پشتیبانی کرده و برای نصب روی لینوکس، مکینتاش، ویندوز و سولاریس بسته‌های نصب ارائه می‌کند. اما از آنجا که قصد ما توسعه این پایگاه داده‌ها در محیط برنامه‌نویسی NET. است، روی بسته مختص ویندوز ۶۴ بیتی آن تمرکز می‌کنیم. اگرچه تمامی مواردی که در اینجا و در‌ مورد کار با این پایگاه داده‌ مطرح می‌شود، روی سایر پلتفرم‌ها مانند لینوکس نیز صادق خواهد بود.
اما پیش از ورود به بحث و کار عملی با MongoDB اگر همچنان در‌مورد دلایل پیشرفت چشم‌گیر پایگاه‌های داده‌ NoSQL در مراکز داده و برنامه‌های کاربردی تحت وب معروف تردید داشته یا علاقه دارید تا پیش از ادامه دادن بحث در‌مورد انواع پایگاه‌های داده NoSQL اطلاعات بیشتری کسب کنید، به شما پیشنهاد می‌کنیم تاویژه‌نامه NoSQL ماهنامه‌شبکه (شماره ۱۳۳) را مطالعه کرده و تمامی اطلاعاتی را که لازم دارید، به‌دست آورید. اما به‌ ‌هر‌ترتیب، اگر اطلاعات گفته شده را دارید یا به‌دلیل این‌که سال‌ها با پایگاه‌های داده رابطه‌ای کار کرده‌اید، حوصله مطالعه آن را ندارید، تنها این نکته را در ذهن داشته باشید که برای کار‌کردن با پایگاه‌های داده NoSQL باید تمامی تصورات خود را از پایگاه‌های رابطه‌ای و دستورات SQL برای انجام پردازش روی داده‌ها را کنار بگذارید و به ابزارهای جدید فراهم شده برای این پایگاه‌های داده‌ای عادت کنید.
پیش از هرچیز لازم است تا پایگاه داده MongoDB را روی دستگاه خود نصب کنید. برای این‌کار به سایت رسمی این پایگاه‌ داده به آدرس mongodb.org مراجعه کرده و پس از مرور به بخش downloads، گزینه متناسب با پلتفرم مورد استفاده خود را – که برای ما ویندوز ۶۴ بیتی است – انتخاب کنید. همیشه آخرین نگارش توصیه شده توسط این پایگاه داده را انتخاب کنید تا به بیشترین ویژگی‌ها با کمترین میزان اشکال و باگ دست بیابید. تنها نکته‌ای که در اینجا باید به آن اشاره داشت آن است که در ستون ویندوز ۶۴ بیتی دو گزینه download و +۲۰۰۸R2* وجود دارد که گزینه دوم برای نصب روی Windows Server 2008 R2 طراحی شده و تنها در صورتی که از این سیستم عامل استفاده می‌کنید، این پکیج را بارگذاری کرده و در غیر‌این‌صورت پکیج موجود در لینک download را دریافت کنید (شکل۱).

نصب MongoDB بسیار آسان است. در واقع تنها کاری که لازم است انجام دهید، خارج کردن محتویات درون فایل بارگذاری شده و ایجاد چند پوشه جداگانه برای نگه‌داری داده‌های آن است. به‌عبارت دیگر اگر شما نیز بسته mongodb-win32-x86_64-2.4.5.zip را بارگذاری کرده باشید، می‌توانید در ریشه درایو C خود یک پوشه با نام mongodb ایجاد کرده و محتویات درون فایل فشرده را بدون ایجاد یک زیر پوشه با نام mongodb-win32-x86_64-2.4.5 درون پوشه mongodb قرار دهید. با این کار، از طریق هر ابزار دیگری، کافی است تا مسیر C:\mongodb\bin را بپیمایید تا به فایل اجرایی mongod.exe برسید که در واقع قلب پایگاه داده MongoDB را تشکیل می‌دهد.
این پایگاه داده همچنین به‌صورت پیش‌فرض مسیر C:\data\db را برای نگه‌داری فایل‌های داده‌ای ساخته شده مورد استفاده قرار می‌دهد. بنابراین، اگر قصد استفاده از این پایگاه داده بدون افزودن تنظیمات دیگری داشته باشید لازم است تا دو پوشه C:\data و C:\data\db را به‌صورت دستی بسازید. پس از آن تنها کافی است تا از طریق command prompt به آدرس C:\mongodb\bin رفته و mongod.exe را اجرا کنید.

پس از آن‌که برای نخستین‌بار mongod.exe را اجرا کردید،‌ به‌احتمال فراوان با هشدار دیوار آتش یا Firewall سیستم‌عامل مواجه خواهید شد. چرا‌که این برنامه روی دو درگاه ۲۷۰۱۷ و ۲۸۰۱۷ فعالیت کرده و نیاز است تا شما اجازه ارسال و دریافت اطلاعات به این برنامه را با انتخاب گزینه شبکه‌های خصوصی (Private Networks) شبکه خانگی (Home Network) یا شبکه کار (Work Network) را بدهید. البته، چنان‌چه از دیوار آتشی به‌غیر از برنامه پیش‌فرض ویندوز استفاده می‌کنید، لازم است تا به تناسب برنامه مورد استفاده، اجازه دسترسی به این درگاه‌ها را به این برنامه بدهید. پس از اجرای mongod.exe اطلاعات فهرست۱ نمایان می‌شود:

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

C:\mongodb\bin>mongod mongod --help for help and startup options Thu Feb 28 14:41:12 [initandlisten] MongoDB starting : pid=2076 port=27017 dbpath=\data\db\ 64-bit host=sobhanattar Thu Jul 13 14:41:12 [initandlisten] db version v2.4.5 Thu Jul 13 14:41:12 [initandlisten] git version: f570771a5d8a3846eb7586eaffcf4c2f4a96bf08 Thu Jul 13 14:41:12 [initandlisten] build info: windows sys.getwindowsversion (major=6, minor=1, build=7601, platform=2, service_pack='Service Pack 1') BOOST_LIB_VERSION=1_49 Thu Jul 13 14:41:12 [initandlisten] options: {} Thu Jul 13 14:41:12 [initandlisten] journal dir=/data/db/journal Thu Jul 13 14:41:12 [initandlisten] recover : no journal files present, no recovery needed Thu Jul 13 14:41:12 [websvr] admin web console waiting for connections on port 28017 Thu Jul 13 14:41:12 [initandlisten] waiting for connections on port 27017

مشاهده پیغام “waiting for connections on port 27017” به‌معنای اجرای موفقیت‌آمیز هسته MongoDB است و شما می‌توانید کار خود را با این پایگاه داده آغاز کنید. توجه کنید که دو درگاه ۲۷۰۱۷ و ۲۸۰۱۷ برای دو مقصود متفاوت مورد استفاده قرار می‌گیرند. به‌عبارت دیگر، MongoDB روی درگاه ۲۷۰۱۷ آماده دریافت اتصال از طریق برنامه‌های مختلف و از درگاه ۲۸۰۱۷ آماده ورود به درگاه مدیریت پایگاه داده است. بنابراین، کافی است تا آدرس http://localhost:28017 را در مرورگر خود تایپ کنید تا پس از فشردن دکمه ورود به صفحه مدیریت پایگاه وارد شوید. در این صفحه دستورات مختلفی برای مدیریت MongoDB از جمله ListDatabases قابل مشاهده است (شکل۲).

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

1	REST is not enabled.  use --rest to turn on.

زیرا برای آن‌که این دستورات بتوانند کار کنند، لازم است تا MongoDB متوقف شده و دوباره دستور mongod.exe با گزینه rest– اجرا شود:

1	C:\mongodb\bin>mongod --rest

پس از گذراندن این مراحل و ورود دوباره به صفحه مدیریت پایگاه داده MongoDB با مرور به http://localhost:28017، قادر خواهید بود تا روی هر یک از دستورات موجود در بالای صفحه مانند listDatabases کلیک‌کرده و نتیجه آن را در قالب یک خروجی JSON مشاهده کنید (شکل۳).

باید از همین حالا به این موضوع عادت کنید. خروجی تمامی دستورات MongoDB در قالب JSON و در صفحه مرورگر به شما ارائه شده و بنابراین، اگر با خواندن این قالب آشنایی ندارید، بد نیست سری به این سایتیا نسخه فارسی آن بزنید. خروجی زیر، نتیجه ارائه شده در قالب JSON برای یک پایگاه داده خالی موجود روی سرور محلی شما است:

1 2 3 4 5 6

{ "databases" : [ { "name" : "local", "sizeOnDisk" : 83886080, "empty" : false } ], "totalSize" : 83886080, "ok" : 1 }

مانند هر پایگاه داده دیگر این امکان وجود دارد تا شما mongod.exe را در قالب یک سرویس خودراه‌انداز (Automatic) به سیستم معرفی کنید تا هربار مجبور به راه‌اندازی دوباره آن نباشید. برای این کار تنها کافی است تا از command prompt دستور mongod را با گزینه –install اجرا کنید. با این‌کار شما راه‌انداز MongoDB را به‌عنوان یک سرویس ویندوزی ثبت کرده‌اید. همچنین با استفاده از گزینه –remove می‌توانید این فرآیند را در هر زمان لغو کنید:

1 2	C:\mongodb\bin>mongod --install C:\mongodb\bin>mongod --remove

ذکر این نکته ضروری است که برای ثبت MongoDB به‌عنوان یک سرویس لازم است تا بعد از گزینه –install از گزینه –logpath نیز استفاده کنید. شکل کلی دستور به‌صورت زیر خواهد بود:

1	C:\mongodb\bin>mongod --install --logpath c:\mongodb\log.txt

در این دستور، فایل log.txt می‌تواند در هر آدرسی و با هر نامی قرار گیرد. در صورت نیاز، می‌توانید لیست کامل گزینه‌های قابل استفاده را در مستندات MongoDB در این آدرس مشاهده کنید.

نحوه بدست آوردن لیست تمامی جدول های پایگاه داده در اس کیو ال لایت

برای بدست آوردن لیست تمامی جداول موجود در یک پایگاه داده SQLITE می توان از دستورات زیر در خط فرمان SQLITE استفاده کرد.

برای لیست همه جداول پایگاه داده مورد نظرتان می توانید از دستور .Tables استفاده کنید، به مثال زیر دقت کنید:

sqlite> .tables

coffees      customers    order_items  orders       salespeople

برای اعمال شرط در واکشی لیست جداول می توانید محدودیت هایی را بر روی کوئری اعمال کنید. مثلا اگر می خواهید فقط لیست جدول هایی که نام آنها با عبارت “ord” شروع می شود را بدست آورید بصورت زیر دستور را وارد کنید:

sqlite> .tables 'ord%'

order_items  orders

دستور زیر لیست جدول هایی را بر می گرداند که انتهای نام آنها با عبارت “ers” به پایان میرسد:

'sqlite> .tables '%ers'

customers  orders

• برای این که مشخص کنید کدام ویژگی جدول ها نمایش داده شود مثلاً فقط نام جدول ها از دستور زیر استفاده کنید:

.schema tablename

• اگر در مورد دستورات قابل استفاده در SQLite نیاز به راهنمایی دارید دستور زیر را در خط فرمان SQLite اجرا کنید:

.help

اما دستورات بالا برای پایگاه داده پیوست شده به پروژه کاربرد ندارند زیرا این دستورات بر روی رابط SQLITE_MASTER پایگاه داده اصلی (Main) اجرا می شوند. در نتیجه اگر از یک پایگاه داده Attach شده مثل مثال زیر:

ATTACH some_file.db AS my_db;

استفاده می کنید، برای بدست آوردن لیست همه جدول ها دستور زیر را اجرا کنید:

SELECT name FROM my_db.sqlite_master WHERE type='table';

توجه داشته باشید که این دستور جدول های موقتی (temporary tables) را نمایش نمی دهد، برای نمایش جداول موقتی باید از رابط sqlite_temp_master استفاده کنید:

SELECT name FROM sqlite_temp_master WHERE type='table';