Archive

Archive for İ

MySQL Stored function\SQL imkanları ilə text parsing(Advance Usage)

Bugünkü mövzu bir xeyli maraqlı olduğu üçün təcili olaraq qeyd etməkdən zövq alıram.
Əlimizdə olan cədvəlimiz:

CREATE TABLE `wd_universities` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `country` tinyint(3) unsigned NOT NULL,
  `city` smallint(5) unsigned NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8

Və içindəki məlumatlardan bir qismi:

*************************** 119. row ***************************
     id: 119
   name: <!--az>9 N-li orta məktəb</az--><!--en>9 N-li orta məktəb</en--><!--ru>9 N-li orta məktəb</ru-->
country: 0
   city: 0
 status: 0
*************************** 120. row ***************************
     id: 120
   name: <!--az>Azerbaycan muellimler inisitutu</az--><!--en>Azerbaycan muellimler inisitutu</en--><!--ru>Azerbaycan muellimler inisitutu</ru-->
country: 0
   city: 0
 status: 0

Gördüyümüz kimi 1 row daxilində

<!--az></az--> , <!--en></en-->, <!--ru></ru-->

tag-ləri arasında məlumatlar var. Bizə lazımdır ki,

<!--en></en-->

tag-ləri arasında olan məlumatı İD-ni verməklə götürək.

İlk öncə

<!--en></en-->

tag-lərinin yer aldığı row-ları match etməliyik. Burda regexp bizə yardım edir:

mysql> select id,name from wd_universities where name regexp '<\!--en>.*</en-->';
.
.
| 107 | <!--az>koopirasiya kolleci</az--><!--en>koopirasiya kolleci</en--><!--ru>koopirasiya kolleci</ru-->                                                                            |
| 108 | <!--az>Gəncə Dövlət Universiteti</az--><!--en>Gəncə Dövlət Universiteti</en--><!--ru>Gəncə Dövlət Universiteti</ru-->                                                          |
| 109 | <!--az>Qafqaz Universiteti</az--><!--en>Qafqaz Universiteti</en--><!--ru>Qafqaz Universiteti</ru-->                                                                            |
| 110 | <!--az>Azerbaycan malliya iqtisad kolleci</az--><!--en>Azerbaycan malliya iqtisad kolleci</en--><!--ru>Azerbaycan malliya iqtisad kolleci</ru-->                               |
| 111 | <!--az>QERB UNIVERSITETI</az--><!--en>QERB UNIVERSITETI</en--><!--ru>QERB UNIVERSITETI</ru--> 

İkinci olaraq bizə bu row-lar daxilində

<!--en>

-li məlumatı çıxarmaq lazımdır. Onu da aşağıdakı sorğu ilə müəyyən edirik:

select 
    substring((select name from wd_universities where
                name regexp '<!--en>.*</en-->' and id = 108),
        locate('<!--en>',
                (select name from wd_universities where
                        name regexp '<!--en>.*</en-->' and id = 108)),
        (locate('</en-->',
                (select name from wd_universities
                    where name regexp '<!--en>.*</en-->' and id = 108))) - 
           (locate('<!--en>',
                (select name from wd_universities
                    where name regexp '<!--en>.*</en-->' and id = 108))));

Nümunə Output:

  <!--en>Gəncə Dövlət Universiteti 

Daha sonra bizə lazımdır ki tag-ləri əvvəldən və sondan silən bir funksiyamız olsun.Funksiya:

delimiter $$
 
CREATE FUNCTION fnStripTags( Dirty varchar(200) )
RETURNS varchar(200)
DETERMINISTIC
BEGIN
  DECLARE iStart, iEnd, iLength int;
    WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
      BEGIN
        SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
        SET iLength = ( iEnd - iStart) + 1;
        IF iLength > 0 THEN
          BEGIN
            SET Dirty = Insert( Dirty, iStart, iLength, '');
          END;
        END IF;
      END;
    END WHILE;
    RETURN Dirty;
END $$

Funksiya istifadəsi:

mysql> select fnStripTags('<!--en>Gəncə Dövlət Universiteti');
+-----------------------------------------------------+
| fnStripTags('<!--en>Gəncə Dövlət Universiteti')     |
+-----------------------------------------------------+
| Gəncə Dövlət Universiteti                           |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Son olaraq və ən əsas olaraq bizim məntiqi sonluq yaradan funksiyamız. Funksiya parametr olaraq İD-ni qəbul edir. Bu İD-yə uyğun olan

<!--en-->

-li məlumatı tapır. Tag-ləri silir və bizə təmiz lazım olan məlumatı çıxardır:

DELIMITER $$
 
CREATE  function exclude_tags_en(u_id int)
RETURNS varchar(150) charset utf8
DETERMINISTIC
BEGIN
    declare v_name varchar(150) charset utf8;
    declare v_last_row_fetched int default 0;
    declare v_result varchar(150) charset utf8;
       
     declare cursor1 cursor for
        select name from wd_universities where name regexp '<\!--en>.*</en-->' and id=u_id;
 
    declare continue handler for not found set v_last_row_fetched=1;
 
    set v_last_row_fetched=0;
    open cursor1;
    cursor_loop: loop
    fetch cursor1 into v_name;
            if v_last_row_fetched=1 then
                    leave cursor_loop;
            end if;
                       
    set @query_parse=(select substring((v_name),
        locate('<!--en>',(v_name)),
        (locate('</en-->',(v_name)))-(locate('<!--en>',(v_name)))));
                       
                set v_result=(SELECT fnStripTags(@query_parse));
                return v_result;      
    end loop cursor_loop;
    close cursor1;
    set v_last_row_fetched=0;
     
END$$

Istifadəsi:

mysql> select exclude_tags_en(108);
+-------------------------------+
| exclude_tags_en(108)          |
+-------------------------------+
| Gəncə Dövlət Universiteti     |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select exclude_tags_en(109);
+----------------------+
| exclude_tags_en(109) |
+----------------------+
| Qafqaz Universiteti  |
+----------------------+
1 row in set (0.00 sec)

Beləliklə tapşırıq yerinə yetirilmişdir.
Təşəkkürlər 😉

Kateqoriyalar: My Stored Routines

MySQL Multi-Table Delete

Bir neçə cədvəldən eyni anda məlumatların silinməsi yəni multiple delete-dən danışacıq.
Yazı boyunca istifadə edəcəyimiz cədvəl və məlumatları yaradaq:

-- cədvəllərin yaradılması 

create table users(
id int not null auto_increment,
`name` varchar(25) not null,
surname varchar(25) not null,
primary key(id)
);


create table users_action(
id int not null auto_increment,
user_id int not null,
u_action_id varchar(25),
primary key(id)
);


create table users_action_list(
id int not null auto_increment,
u_action_name varchar(25) not null,
primary key(id),
unique(u_action_name)
);

-- məlumatların daxil edilməsi
insert into users(`name`,surname) values('Shahriyar','Rzayev'),('Emin','Rehmanov'),('Arif','Shamistanli');
insert into users_action_list(u_action_name) values('shopping'),('running'),('administration');
insert into users_action(user_id,u_action_id) values((select id from users where `name`='Shahriyar'),(select id from users_action_list where u_action_name='shopping'));
insert into users_action(user_id,u_action_id) values((select id from users where `name`='Shahriyar'),(select id from users_action_list where u_action_name='running'));
insert into users_action(user_id,u_action_id) values((select id from users where `name`='Shahriyar'),(select id from users_action_list where u_action_name='administration'));

İndi isə multiple delete-in 2 fərqli syntax-ına baxaq. Lakin ilk öncə silinəcək row sayını hesablayaq:

select count(*) from users where `name`='Shahriyar';

# count(*)
1

select count(*) from users_action where user_id=(select id from users where `name`='Shahriyar' );

# count(*)
3

3+1=4 yəni 2 cədvəldən 4 row silinməlidir. 2 yolla bunu edə bilərik:

-- 1-ci üsul

delete u , uac from users as u
        inner join
    users_action as uac ON u.id = uac.user_id 
where
    u.id = 1;
Query OK, 4 rows affected (0.16 sec)

-- 2-ci üsul

delete from u,uac
      using users as u inner join users_action as uac
     ON u.id = uac.user_id 
where
    u.id = 1;
Query OK, 4 rows affected (0.17 sec)

Bəli hər 2 halda 4 row silindi.
Təbii ki multiple delete yalnız 2 cədvəl ilə sərhədlənmir 3 cədvəldən də eyni anda silə bilirik:

delete u , uac , uacl from users as u
        inner join
    users_action as uac ON u.id = uac.user_id
        inner join
    users_action_list as uacl ON uac.u_action_id = uacl.id 
where
    u.id = 1 and uac.u_action_id = 1;

3 row(s) affected

Bundan əlavə mənə maraqlı gələn bir xüsusiyyət də mövcuddur ki, bir neçə cədvəldən axtarış edib daha sonra da yalnız bizə lazım olan cədvəldən məlumatları silə bilirik. Diqqət edək:

delete u , uac from users as u
        inner join
    users_action as uac ON u.id = uac.user_id 
		inner join
	users_action_list as uacl on uac.u_action_id=uacl.id
where
    u.id = 1 and uac.u_action_id = 1;

2 row(s) affected

Əgər diqqət etdinizsə axtarış 3 cədvəldən lakin silinmə 2 cədvəldə yerinə yetirildi və dolayısı ilə 3-cü cədvəldəki 1 row silinmədi və toplam 2 row silinmiş oldu.

Multi-table delete-lər haqqında daha ətraflı dokumentasiyadan oxuya bilərsiniz:
Multi-table delete DOC

Təşəkkürlər 😉

Excessive privilege injection in MySQL (sql injection read/write directory)

/* Xahiş olunur php developerlər php kodda səhv tutub mənasız mübahisə aparmasın. Yazı məqsədi php tərəfdən injection-un qarşısı alınması deyil nə də ki, php best practice məqalə deyil */

MySQL user-ləri yaradarkən həddindən artıq verilən grant-ların nəticəsi olaraq bir çox təhlükəsizlik problemi yaşanır ki, onlardan mənim üçün ən diqqətə layiqi FİLE privilege-dir.
İlk öncə simple PHP search yaratmağımız lazımdır.
index.php-də form yerləşəcək search2.php-də isə db connection və select yerləşəcək:
index.php:

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title></title>
    </head>
    <body>
        <form method="get" action="search2.php">
            
            <input type="text" name="title" />
            <input type="submit" name="search" />
            
        </form>
    </body>
</html>

search2.php:

<?php
error_reporting(E_ALL);
ini_set('display_errors', True);


$db = new mysqli('localhost', 'root', '12345', 'test');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}


$find=$_GET['title'];

$sql="SELECT * FROM book WHERE title like '$find'";

// var_dump($sql);

if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}

while($row = $result->fetch_assoc()){
    echo $row['id'].'<br />';
    echo $row['title'] . '<br />';
    echo $row['content'];
}

$db->close();

?>

Gördüyünüz kimi root user-dən istifadə olunur və həqiqətən də bir çox production server-də bunu müşahidə etmək olur. Təbii ki biz ümumi ziyanları haqqında yox məhz FİLE privilege-dən danışacıq. İlk öncə root user nədir? və hansı privilege-ləri var?:

mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' WITH GRANT OPTION
+----------------------------------------------------------------------------------------------------------------------------------------+

ALL PRIVILEGES ON *.* = bütün database object-ləri üzərində istənilən əməliyyatı yerinə yetir.
ALL PRIVILEGES= Read about ALL
Burda diqqətimizi FİLE privilege çəkir. From documentation:
The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT … INTO OUTFILE statements and the LOAD_FILE() function. A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.) The FILE privilege also enables the user to create new files in any directory where the MySQL server has write access. This includes the server’s data directory containing the files that implement the privilege tables. As a security measure, the server will not overwrite existing files.

search2.php-ni işlədək:

Daha sonra SQL injection pattern-imizi yazaq:

Bəli gördüyünüz kimi Union-based SQLi aşkar olundu. /etc/passwd file-nı bu yolla çəkmək olar mı?:

Və bu üsuldan istifadə edib biz hər hansı cədvəldə olan məlumatı bir .txt fayla yazıb daha sonra da onu yuxarıdakı şəkildə oxuya bilərik. information_schema-dan istifadə edib eyni şəkildə cədvəl məlumatlarını çəkmək olar. Lakin dediyim kimi mövzumuz FİLE privilege-dir.

mysql.user yəni grant cədvəlində(mysql server-in user-lərinin qeyd olunduğu cədvəl) olan username və pass-i götürmək istəsək:

bizim /tmp directory-mizdə həqiqətən də nnc8.txt faylının yarandığının şahidi oluruq:

root@shrzs:~# ls -l /tmp | grep nnc8.txt
-rw-rw-rw- 1 mysql     mysql      350 İyl  7 12:47 nnc8.txt

və .txt faylın içindəki məlumatları oxuyaq:

Bəli gördüyünüz kimi istənilən cədvəlin məlumatlarını bu şəkildə dump edib daha sonra da oxuya bilərik.
Qarşısının alınması- müvafiq grant-ın verilməsi:
User yaradırıq və grant veririk:

mysql> create user 'testing'@'localhost' identified by '12345';
Query OK, 0 rows affected (0.04 sec)

mysql> grant select,insert,delete,update on test.* to 'testing'@'localhost';
Query OK, 0 rows affected (0.05 sec)

Məhz bu user-lə connect olub select verdikdə:

mysql> SELECT load_file('/tmp/nnc8.txt');
+----------------------------+
| load_file('/tmp/nnc8.txt') |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)

mysql> select user,password from mysql.user into outfile '/tmp/nnc9.txt';
ERROR 1045 (28000): Access denied for user 'testing'@'localhost' (using password: YES)

Bəli gördüyümüz kimi, yeni user-imiz directory-dən məlumat oxuyub və yaza bilmir.
3-cü video tutorialı security mövzusunda hazırlamaq fikrindəyəm. video-da mövzunu genişləndirəcik.

Təşəkkürlər 😉

Kateqoriyalar: MySQL Etiketlər: ,

MySQL strict mode-un aktiv edilməsi (tutorial N2)

2-ci dərsimizdə MySQL-in strict şəklinə gətirilməsindən söhbət açmışıq.
sql_mode ilə MySQL Error handling-in necə reallaşa biləcəyini göstərmişik.

Kateqoriyalar: MySQL Video Tutorials

İlkin MySQL konfiqurasiya faylının hazırlanması(tutorial N1)

Bu mənim MySQL-in intermediate və advance şəkildə istifadəsindən bəhs edən video tutorial seriyamdan 1-ci dərsdir.
Bu dərsdə Linux üzərinə fresh install olunmuş MySQL üçün ilkin konfiqurasiya faylının hazırlanmasından söhbət etmişik.
Mütləq şəkildə bu konfiqurasiya faylını siz də özünüz üçün hazır edin, çünki bundan sonrakı dərslərin hamısı məhz bu konfig fayl üzərində qurulacaq.

Kateqoriyalar: MySQL Video Tutorials

Emulating Oracle’s rownum in MySQL

Oracle-dan bilinən çox faydalı bir xüsusiyyətdən söhbət açırıq. Qısa olaraq:
ROWNUM is an Oracle pseudo column which numbers the rows in a result set.

Yəni ROWNUM select-dən neçə ROW qayıtdığını bizə göstərir:

    SELECT rownum, table_name
    FROM user_tables;

    ROWNUM        TABLE_NAME                     
    ------------- -----------------
    1             EMP
    2             DEPT
    3             BONUS
    4             SALGRADE
    5             DUMMY

    5 rows selected

Belə bir xüsusiyyət MySQL-də olmadığı üçün biz workaround fikirləşib tapmalıyıq.

2 üsulla buna nail ola bilərik.
Cədvəlimizdə 30 row var:

mysql> select department_name from departments;
.
.
30 rows in set (0.00 sec)

ROWNUM-ı aşağıdakı şəkildə generate edə bilərik:

select 
    @rownum:=@rownum + 1 as rownum, 
	department_name
from
    (SELECT @rownum:=0) as r
        inner join
    departments;

+--------+--------------------+
| rownum | department_name    |
+--------+--------------------+
|      1 | BERKSHIRE          |
|      2 | CAMDEN             |
|      3 | CHAMPAIGN          |
|      4 | CHARITON           |
.
.
.
|     26 | TAZEWELL           |
|     27 | VENTURA            |
|     28 | WHATCOM            |
|     29 | WILKES             |
|     30 | WOOD               |
+--------+--------------------+
30 rows in set (0.00 sec)

2-ci üsulda isə session variable-dan istifadə edəcik yəni join-siz:

-- 1
set @row=0;

-- 2
select 
	@row:=@row+1 as rownum,
    department_name
from
	departments;

+--------+--------------------+
| rownum | department_name    |
+--------+--------------------+
|      1 | BERKSHIRE          |
|      2 | CAMDEN             |
|      3 | CHAMPAIGN          |
|      4 | CHARITON           |
.
.
.
|     26 | TAZEWELL           |
|     27 | VENTURA            |
|     28 | WHATCOM            |
|     29 | WILKES             |
|     30 | WOOD               |
+--------+--------------------+
30 rows in set (0.00 sec)

Bunda əlavə əgər biz tapılan row sayını öyrənmək istəyiriksə sql_calc_found_rows-dan istifadə edə bilərik:

select 
	sql_calc_found_rows
    department_name
from
    departments;

və daha sonra da :

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|           30 |
+--------------+
1 row in set (0.08 sec)

Təşəkkürlər 😉