SELECT *  FROM (

     SELECT * FROM TABLE WHERE X = X

          OREDER BY price ASC

)  GROUP BY code ORDER BY ORDER

 

Mysql 에서 가격이 데일리로 들어가 있는경우 낮은 가격을 가져오고 서브쿼리에서 크룹핑하고 순서를 줬다

 

마리아 DB에선 내부쿼리에서 order 불허!!!!!!!!

 

편법 생각하자.....

SELECT *  FROM (

     SELECT * FROM TABLE WHERE X = X

          GROUP BY code , price

)  GROUP BY code ORDER BY  order

 

내부쿼리에 그룹에 두번째 디폴트 낮은 순이니 다시 그룹으로 ㄱㄱ

 

블로그 이미지

스마트전

,

character 한번에 바꾸기

MySQL 2019. 10. 16. 18:36

alter table table_name convert to character set UTF8;

블로그 이미지

스마트전

,

달력 숫자 채우기

MySQL 2019. 10. 16. 18:35

create table t (n int);

 

insert into t values (1);

 

insert into t select * from t; -- 이걸 13번 반복하면 4096행이 생성됨. 10년치 데이터라면 대략 3650일이므로 이정도면 충분

 

create table date_t (d date, ds char(8)); -- 날짜를 저장할 테이블

 

insert into date_t

select d, date_format(d, '%Y%m%d') from (

  select @rnum:=@rnum+1 as rownum, date(adddate('2009-01-01', interval @rnum day)) as d

  from (select @rnum:=-1) r, t

  ) t

where year(d) < 2019;

 

참고로, 날짜는 date 타입에 저장하는 것이 yyyymmdd형식의 문자열로 저장하는 것보다 좋습니다

 

insert into high_block (stay_code , room_code , r_block ,  chk_date )

SELECT 'high01' , '1001' , 0 , date_format(d, '%Y-%m-%d') from (

select @rnum:=@rnum+1 as rownum, date(adddate('2019-01-01', interval @rnum day)) as d

from (select @rnum:=-1) r, t

) t

where year(d) < 2021;

 

 

은근 많이 쓰는거

블로그 이미지

스마트전

,

MySQL IFNULL

MySQL 2012. 12. 3. 18:23

MySQL IFNULL()의 사용



IFNULL은 해당 필드의 값이 NULL을 반환할때 다른 값으로 출력할 수 있도록 하는 함수이다. 


SELECT IFNULL(필드명, "대체할 값") FROM 테이블명; 


물론 하나의 필드의 값 뿐만아니라 , 연속적으로도 활용 할 수 있다. 


다음은 IFNULL을 사용한 예이다. 




위의 데이터는 3개의 필드에서 무작위로 NULL 값이 존재한다. 하지만 3개의 필드중 값이 존재한다면 그 값은 동일한 경우이다. 

만약 keyword 와 3개의 필드의 값중 하나를 출력하려 한다면 IFNULL을 활용하여 다음과 같이 사용할 수 있다. 


$query = "SELECT keyword, IFNULL( common_rep, IFNULL( fur_rep, makeup_rep ) ) as syn_2 FROM syn_color"; 


  출력 결과 



블로그 이미지

스마트전

,

테이블에 들어 있는 특정 문자열을 다른 문자열로 바꾸고 싶을 때...replace를 사용하면 된다.

예를 들어 products 라는 테이블에 있는 origin이라는 컬럼의 china를 korea로 모두 바꾸고 싶다면...

UPDATE products SET origin=REPLACE(origin, "china", "korea")



이런 식으로 phpMyAdmin SQL 탭에 명령을 적어넣은 후 실행하면 된다.
만약 특정 회사의 것만 바꾸고 싶다면 뒤에 WHERE 절을 넣으면 된다.

UPDATE products SET origin=REPLACE(origin, "china", "korea") WHERE company="mycompany"



DB를 다른 곳으로 옮긴 뒤 몇 가지 수정할 일이 있었는데 매우 편리하게 금방 처리가 되었다.

 펌 http://jubuntu.tistory.com/94

'MySQL' 카테고리의 다른 글

달력 숫자 채우기  (0) 2019.10.16
MySQL IFNULL  (0) 2012.12.03
MySQL / FullText에서 특정 단어를 빠르게 검색하기  (0) 2012.08.30
SQL 인젝션 공격 방어  (0) 2012.07.31
SQL injection, SQL 주입 공격, SQL 주입 취약점  (0) 2012.07.31
블로그 이미지

스마트전

,
보통 MySQL에서 FullText에 대해서 단어를 검색할 때 LIKE "%words%"를 주로 사용하기 마련이다.
하지만 이는 대용량 데이터에서 검색할 때는 느린 쿼리 결과를 보여준다.

먼저 FullText에 인덱싱을 걸어주자~!

CREATE FULLTEXT INDEX idx_1 ON my_search (text_data);
 

FullText에 대해서 빠르게 검색할 수 있는 방법이 있으니 MATCH, AGAINST 문법을 사용하면 된다.
FullText Indexing을 사용하는 방법으로 사용방법은

1. 기본적으로 words 에 대해 검색하고자 할 때
1 select * from table_name where MATCH(column_name) AGAINST("words")

2. words1과 words2를 포함한 것을 찾고자 할 때(이 때는 뒤에 IN BOOLEAN MODE라고 작성해 주어야 한다.)
1 select * from table_name where MATCH(column_name) 
2 AGAINST('+"words1" +"words2"' IN BOOLEAN MODE)

3. word1과 words2는 포함하고, words3는 포함하지 않는 것을 찾고자 할 때
1 select * from table_name where MATCH(column_name) 
2 AGAINST('+"words1" +"words2" -"words3"' IN BOOLEAN MODE)

참고문헌
http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

http://snowple.tistory.com/345

'MySQL' 카테고리의 다른 글

MySQL IFNULL  (0) 2012.12.03
테이블 내 특정 문자열 한꺼번에 바꾸기 - REPLACE  (0) 2012.08.30
SQL 인젝션 공격 방어  (0) 2012.07.31
SQL injection, SQL 주입 공격, SQL 주입 취약점  (0) 2012.07.31
mysql 정리  (0) 2012.01.06
블로그 이미지

스마트전

,

SQL 인젝션 공격 방어

MySQL 2012. 7. 31. 14:10

sql 인젝션 공격이란? 

참고 : http://blog.naver.com/myefhc/100119445401

 

sql 인젝션 방어

참고 : http://kr2.php.net/manual/en/security.magicquotes.whynot.php

출처 : http://blog.naver.com/tinenie/110016874721

출처 : http://b.mytears.org/tag/array_map

 

 

==================================================================================================

 

sql injection 을 피하기 위해 single quote 를 escape 시켜주는 코드를 작성해야할 필요가 있었는데, 매번 하나하나에 대해 mysql_real_escape_string 을 호출해주는 건 너무나도 번거로웠다. 뭔가 간단하게 처리할 방법이 없을까 하고 찾아봤더니 왠걸! array_map 이라는 마법의 함수를 발견할 수 있었다.

function stripslashes_deep($var){
    $var = is_array($var)?
                  array_map('stripslashes_deep', $var) :
                  stripslashes($var);
 
    return $var;
}
 
function mysql_real_escape_string_deep($var){
    $var = is_array($var)?
                  array_map('mysql_real_escape_string_deep', $var) :
                  mysql_real_escape_string($var);
 
    return $var;
}
 
if( get_magic_quotes_gpc() ){
    if( is_array($_POST) )
        $_POST = array_map( 'stripslashes_deep', $_POST );
    if( is_array($_GET) )
        $_GET = array_map( 'stripslashes_deep', $_GET );
}
 
if( is_array($_POST) )
    $_POST = array_map( 'mysql_real_escape_string_deep', $_POST );
if( is_array($_GET) )
    $_GET = array_map( 'mysql_real_escape_string_deep', $_GET);

common function 들을 정의해놓은 파일 맨 아래 저 코드를 삽입해버리니 sql injection 따위 이제 두려워할 필요가 없어졌다. 움하하핫!!

p.s) 그냥 array_map 에 stripslashes 나 mysql_real_escape_string 을 사용하게 되면 array 가 넘어온 경우 문제가 생길 수 있어서 약간 수정을 했습니다.

 

==================================================================================================

 

#### 1. 일반 SQL

 $query = "select userid, password from user_table where userid='" . $_POST["userid"] . "' and password = '" . $_POST["password"] . "'";

 

userid 가 "username' #"  이면 password 부분은 주석 처리됨..

  - MySQL 에서 # 가 주석

  - Oracle 에서 -- 가 주석

 

##==> 해결

' 를 못 쓰게 하면 된다.

mysql 에서 다음 함수 이용 or addslashes() 함수 이용..

  - mysql_escape_string()
  - mysql_real_escape_string()

 

 

========================================

#▶mysql SQL 사용 변수 check
# ' 를 사용 못하게 한다.
# 특수 문자가 하나라도 있으면 false 반환
function mysql_check( $array ) {
    foreach ($array as $str) {
        $pos = strpos($str, "'");
        if ($pos === false) continue;

        return false;
    }


    return true;
}

 

# SQL에 사용할 변수 검사

if (!mysql_check( array($email, $passwd) )) echo_error("검색 실패");

 

#### 2. ' 가 없는 SQL

 $query = "select * from user_table where type=". $_POST["type"] . "' ";

 

type 에 주석(#) 및 종료 문자( ; ) 등을 이용하여 공격을 할 수 있다.

 

##==> 해결

php에서 제공하는 escape_string 를 이용하는게 좋습니다.

 

  mysql_escape_string() 
  pg_escape_string() 
  sqlite_escape_string() 

$_POST = array_map('mysql_escape_string', $_POST);

 

 

#################################

# Reference

#################################

 

php|architect's Guide to PHP Security 라는 책의 Chapter 3, SQL Injection 내용이 Mysql 사이트에 공개되어 있습니다. 

http://dev.mysql.com/tech-resources/articles/guide-to-php-security.html 

블로그 이미지

스마트전

,

----

SQL 주입 공격 [ SQL injection, -注入攻擊 ]
 
웹 클라이언트의 반환 메시지를 이용하여 불법 인증 및 정보를 유출하는 공격. 웹 응용 프로그램에 강제로 구조화 조회 언어(SQL) 구문을 삽입하여 내부 데이터베이스(DB) 서버의 데이터를 유출 및 변조하고 관리자 인증을 우회할 수도 있다. 이 공격은 MS SQL 서버뿐만 아니라 모든 관계형 데이터베이스 관리 시스템(RDBMS)에서 가능하다.

 

SQL 주입 취약점 [ SIV, SQL Injection Vulnerability, -注入脆弱點 ]

입력문에 구조화 조회 언어(SQL)문에 대한 필터링이 없을 경우 해커가 SQL문으로 해석될 수 있는 입력을 시도하여 데이터베이스에 접근할 수 있는 보호 취약점. 웹 브라우저 주소(URL)창 또는 사용자 ID 및 패스워드 입력 화면 등에서 데이터베이스 SQL문에 사용되는 문자 기호(‘ 및 “)의 입력을 적절히 필터링하지 않은 경우에 해커가 SQL문으로 해석될 수 있도록 조작한 입력으로 데이터베이스를 인증 절차없이 접근, 자료를 무단 유출하거나 변조할 수 있다. 예를 들어 관리자 ID와 패스워드에 아래 문자열을 입력했을 때 로그인되면 취약점이 존재한다. ID : ’or 1=1 ; -- 패스워드 : ’or 1=1 ; --

 

출처 : 네이버 용어사전

----

 

----

SQL injection

웹 사이트의 취약점을 이용해 권한 없는 사용자가 정보를 할 수 있는 공격기법이라고 합니다.

  

SQL injection에대해 설명 드리기전에 Datebase와 Query문에 대해 조금은 알고 계셔야 하는대

기초 과정이니까 보시면서 이해하시는 방향으로 진행하겠습니다

 

DateBase에

test 테이블이 있고 그곳에 필드 userid와 userpw 가있다고 치고

그 필드 각각 userid 부분엔 nisam   userpw부분엔 1234 이란 값 저장되어 있다고 치고

다음 Query문을 보면서 설명드리겠습니다.

 

Query문은

select 필드명1,필드명2 from 테이블명;

이런식으로 구성되는대 위에 만든 DB로 설명드리자면

 

 select uesrid,userpw from test where userid='nisam' and userpw='1234'

 

 

 

 라고 입력하고 Query문을 위에 만든 DB에 보내게 된다면

 

  userid='nisam'(true)  userpw='1234' (true)

 

 

 

 가 되어 true and true = true로 정상적으로 작동하게 됩니다.

 

  select uesrid,userpw from test where userid='nisam' and userpw='1023'

 

 

 

 만약 이런 Query문을 입력해서 보내게 된다면 어떻게 될까요?

 

  userid='nisam'(true)  userpw='1023' (false)

 

 가되어 true and false = false로 정상적으로 작동하지 않게 됩니다.

 

 

 

이제 직접 SQL injection을 해보면서 원리를 파악해보록 합시다.

 

이곳으로 접속해보세요

(이곳은 데모사이트 인대 웹해킹을 아무곳에서나 시도하면 잡혀갑니당)

 

 

 

 

이러한 로그인 창이 뜨는대요. 이 사이트의 SQL문을 추측해보자면

select uesrid,userpw from account where username='입력값' and password='입력값'

라고 추측할 수 있습니다 ( 필드명이나 테이블명 따윈 중요하지 않음)

이제 여기다가 아무값이나 입력해서 Query문을 보내보도록 합시다.

 

 

아이디와 비밀번호를 asdf로 입력해서 로그인을 시도한다면 아이디가 없다고 뜨고 로그인이 되지않습니다. 그이유는

 

  select uesrid,userpw from account where username='asdf' and password='asdf'

 

 

 

 이라는 Query를 보내게 됬을탠대

 

  username='asdf'(false) and password='asdf'(false)

 

 

 

 아이디가 존재하지 않아 비정상적으로 처리되어서 로그인이 되지 않았는겁니다.

 

 

 

다음엔 아이디와 비밀번호에 각각 'a을 입력하고 로그인해보세요. 이번에도 로그인이 안될탠대

아까와는 나오는 페이지가 조금 다를겁니다.

 

 

바로 에러 메세지 창이 뜨게 됩니다. 그이유는 Query문을 보게되면

 

  select uesrid,userpw from account where username=''a' and password=''a'

 

 이러한 방식으로 보내졌을탠대

 

  username=''a' and password=''a'

 

 

 

 '로 시작하고 '로 끝나야 하는대 이미 끝났는대 a가 나오게 되어 오류가 발생한것 입니다.

 

 

이제 위에 발생한 오류를 이용하여 Query문을 살짝 수정하여 원하는 값을 얻게 되는 SQL injection을

해보도록 하겠습니다.

 

 

아이디 부분에 admin 비밀번호에 'or'1'='1 을 입력하고 로그인 한다면 어떻게 될까요?

 

 

 

로그인이 성공하였습니다. 왜 로그인이 성공한걸까요 Query문을 보게되면

 

  select uesrid,userpw from account where username='admin' and password=''or'1'='1'

 

 

 

 이런식으로 보내졌을탠대

 

  username='admin'(true) and password=''(false)or'1'='1'(true)

 

 

 

 true and (false or ture = true) = ture

 

 공백은 비밀번호가 아니기 때문에 false이고 1=1는 true 이니까 or로 인해 true가 된겁니다.

 이해가 가시나요? 패스워드 부분에서 or을 넣어서 강제로 true가 되게 만든겁니다.

 

 

위에서 쓴 방법과 다르게 주석을 사용하여 Qurey문을 조작해서 로그인 해보도록 하겠습니다

SQL문에서의 주석은 --로 사용되어 --뒤에 입력된 부분은 모두 삭제되어 전송됩니다.

 

 

 로그인에 admin'--  비밀번호에는 아무거나 입력하시고 로그인 하시면 역시나

 

 

로그인에 성공하게 됩니다. 역시 Query문을 보면

 

  select uesrid,userpw from account where username='admin'--' and password='asdfasdd'

 

 

 

 이렇게 되는대 실제로 주석 때문에 처리된 SQL문은

 

  select uesrid,userpw from account where username='admin'(true)

 

 

 

 이로써 아이디부분이 true가 되어 로그인이 성공하게 됩니다.

 

 

대응방안

대응 방안으로는 데이터의 길이에 제한을 두는 방법이 있지만 이 방법보다는

로그인과 비밀번호 입력할때 특수문자의 제한을 두는 방법 또는

오류창을 이용자들에게 보여주지 않아 취약점을 쉽게 찾아낼 수 없게 하는 방법이 있습니다.

 

출처 : http://drew03011.blog.me/100105788412

----

'MySQL' 카테고리의 다른 글

테이블 내 특정 문자열 한꺼번에 바꾸기 - REPLACE  (0) 2012.08.30
MySQL / FullText에서 특정 단어를 빠르게 검색하기  (0) 2012.08.30
SQL 인젝션 공격 방어  (0) 2012.07.31
mysql 정리  (0) 2012.01.06
mysql date 함수  (0) 2012.01.05
블로그 이미지

스마트전

,