Saturday, November 04, 2006

Beware of SQL Injection

SQL injection is one of the simple but very powerful security threat, that is also very common in various sites.

Let us say we have a website, and we want to let only the registered users to login by asking for username and password. Let us say we store the username and password in the database.

To check whether the user is valid or not, we use the following query.


select * from users where username='abcd' and password='xyz'


If the result contains atleast 1 row, we say the user name password matches.

A typical Java code will be,


String username = "abcd"; // or get from the user.
String password = "xyz"; // or get from the user.

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
"select * from users
where username='"+username+"'"
and password='"+password+"'"
);
if(rs.next()){
// user is logged in.
}else{
// login failed.
}


As we are simply concatenating the string, we don't have a clue what the user will type for the username and password fields.

What happens if the user types ' or ''=' including the quotes?
Then, the SQL query will be

select * from users where username='abcd' and password='' or ''=''

This will succeed irrespective of the username or password the user types. This will clearly be a security threat.

How to fix this?
Should we check for the input string whether it contains any single quote and ignore if any? That will be very difficult to check for each and every field.
A simple technique is, use PreparedStatement.

That is the code will then be,

String username = "abcd"; // or get from the user.
String password = "xyz"; // or get from the user.

PreparedStatement stmt = con.prepareStatement("select * from users
where username='?'
and password='?'"
);
stmt.setString(1,username);
stmt.setString(2,password);

ResultSet rs = stmt.executeQuery();

if(rs.next()){
// user is logged in.
}else{
// login failed.
}

When the user types the password as before, it wont allow the user to login as the prepared statement takes care of escaping the characters, there by guaranteeing the expected behavior.

Conclusion:
Always try to use PreparedStatement instead of String concatenations in all places.

48 Comments:

Anonymous venkat said...

i dunno about java, but with the python db api, you could do something like
cursor.execute("select * from tbl where name=%s and passwd=%s", (name,pass))

where the second argument is a tuple, substituted for the place holders. and the api takes care of the escaping accoring to the database spec, that sql injection seems impossible.

also, most web programmers might be using some framework, in which case, the framework would provide some customizable authentication mechanism (possibly written by a smarter person) which would invalidate such attacks.

10:38 AM  
Blogger Jayaprabhakar said...

Thanks for visiting my Blog, Venkat.
The SQL Injection problem exists in almost all languages where String concatenation is present.

Eg: Even in Python
//
cursor.execute("select * from tbl where name="+name+" and passwd="+pass)
//
(I am not sure about the syntax for string concate in Phython :) )
Because of carelessness, even experienced programmers do this mistake, which is very simple to avoid.

5:09 AM  
Blogger test_blogs123 said...

This comment has been removed by the author.

12:16 PM  
Blogger test_blogs123 said...

JP, just saw your response :)... but, usually, the method i have said is only followed... yes, i agree, the fundamental rule is to allow the underlying SQL api to do the string escaping...

cross side scripting is one more (also well known) interesting attack

12:18 PM  
Blogger Krishnakanth Soni said...

Great Blog !!! Found very interesting !!!

7:56 AM  
Blogger Tarun said...

I read your blog and thought I could tell you about something else which would be useful for seeing maps and directions in India.

I am writing to tell you about MapmyIndia.com, a free interactive maps and directions portal for all India. See the map of connaught place, new delhi, get directions in mumbai from nariman point to juhu airport, and find nearby ATMs in kormangala, bangalore.

As a company and individual enthusiasts, we dream only of solving the problem of reliable directions and navigation for India. For your blog specifically, you can map enable it by using our youtube-style embeddable maps, and links to specific searches (of maps, directions, local and eLocation) on MapmyIndia.

Do give us feedback, suggestions, or get involved yourself by mailing me back at tarun@mapmyindia.com or marketing@mapmyindia.com.

And if you find the different services useful, we would be grateful to you for writing and telling your readers about us.

Warm Regards,
Tarun Gupta
The MapmyIndia Team
For directions in India, just search print and go with MapmyIndia.com

11:24 PM  
Blogger 週休 said...

Never put off till tomorrow what may be done today..........................

2:14 AM  
Blogger 傷口很殺 said...

辛苦了!祝你愈來愈好! .........................................

12:48 AM  
Blogger ya said...

感謝是愛心的第一步........................................

10:05 PM  
Blogger 與發 said...

乳交挑逗淫婦色情俱樂部成人色情成人影片視訊網愛聊天室免費色情網情色區火辣美女情色性愛成人影音聊天成人色情網站全裸美女圖片成人裸照sex辣妹裸體美女全裸圖成人區av女情色內容情色自拍貼圖成人色情網性愛論壇一對多性伴侶成人聊天室撫摸淫美成人論壇女人奶頭女生自慰影片台灣女優美女視訊一絲不掛一夜正妹成人影像巨乳大奶子情色性愛貼圖情色王國做愛視訊火辣情色台灣情色網情色聊天網性愛技巧淫娃情色成人巨乳辣妹性愛知識性經驗

1:53 AM  
Blogger 林淑凡 said...

向小善致敬,它使人生旅程較為平順。........................................

4:21 AM  
Blogger 上宜俊宇芳心 said...

人有兩眼一舌,是為了觀察倍於說話的緣故。......................................................

11:19 PM  
Blogger G702aynelleKress0 said...

Many a little makes a mickle.........................................

4:55 AM  
Blogger LoydByrnes214 said...

感謝是愛心的第一步..................................................

11:33 PM  
Blogger EmogeneJ said...

謝謝您的分享~感恩唷!!..................................................

11:46 PM  
Blogger ManualDoby08787 said...

鴛鴦吧成人影片免費av成人電影線上免費成人網gogo成人影片a成人短片歐美性愛免費片歐美性愛網歐美性愛寫真歐美情色區歐美情色影片放映區歐美模特兒貼圖歐美模特兒圖歐美線上色情片歐美線上電影歐美線上論壇歐美整外潮吹影片免費看線上下載無碼遙瑤熊貓列表熊貓貼圖站熊貓貼圖區區熊貓貼?熊貓圖片網熊貓圖貼區熊貓影片瘋情網路色情電影網路色情電影觀賞網路看片小遊戲下載區視訊美女館情色a片美女遊戲

10:44 AM  
Blogger Elvi107sS_Scholten0 said...

沈舟側畔千帆過,病樹前頭萬木春......................................................

9:07 AM  
Blogger 517SeritaG_Jamar0 said...

黑色豪門企業綜合娛樂論壇 ez洪爺的家 嘟嘟情人色網dvd hot辣妺視訊網 限制性漫畫 一夜情人視訊 av1688大天使娛樂網 bt成人網 go2av成人聊天室 免費a長片線上看,女優影片 無碼 av影片 美眉 美女 聊天室 遊戲區 18成人http 17hi tw 情色聊天 百分百貼圖區亞洲avdvd 免費視訊聊天mm17i 情色a片 88天下淫書,少年阿賓系列小說,中文情色文學小說 0401線上影城 視訊做愛 杜蕾斯成人 亞洲辣妹妹影音視訊聊天室 美女聊天室 麗的情色小遊戲 韓劇人妻的秘密85cc影城 playboy國際中文網 免費影音視訊hibb 6k情人網暗戀視訊 免費視訊4h 無碼av女優微風成人區 甜心寶貝直播貼片aio交友愛情館 xvediox免費影片 視訊交友520show net 視訊美女聊天 kk 台灣kiss911h影片線上a片 aaaa 片俱樂部 2girl女子拉拉學園 av168成人網 辣妹影片直播台南援交友留言 omyga美色女影城 go2av免費影片情色 網站 ut聊天室kww 情人視訊高雄網 性行為補給站 後宮視訊聊天網 網愛mmshow 主播情人視訊情色交友 104愛戀速配網 視訊美女聊天室 免費色咪咪影片網 兼職援交 影音視訊聊天室dudu sex

9:11 PM  
Blogger 韋成 said...

Virtue dwells not in the tongue but in the heart. ............................................................

7:10 AM  
Blogger 家谷 said...

我們不是因為快樂而歌唱,而是唱歌使我們快樂..............................

8:25 PM  
Blogger ShilaLong嘉雯 said...

所有的資產,在不被諒解時,都成了負債..................................................

3:40 AM  
Blogger sdas said...

健康的身體是事業成功的基礎。 ............................................................

8:08 PM  
Blogger Kath黃rinA俊宇 said...

人有兩眼一舌,是為了觀察倍於說話的緣故。..................................................................

11:13 AM  
Blogger 曉薇 said...

要照顧身體歐~保重

11:15 AM  
Blogger Elvis湘均Kasp湘均 said...

良言一句三冬暖,惡語傷人六月寒。.................................................................

4:40 AM  
Blogger 幸齊幸齊 said...

欣賞是一種美德~回應是最大的支持^^.................................................................

6:54 PM  
Blogger 泓發 said...

當一個人內心能容納兩樣相互衝突的東西,這個人便開始變得有價值了。............................................................

4:07 AM  
Blogger 鄭湘定 said...

It takes all kinds to make a world.............................................................

1:18 AM  
Blogger 蕙春蕙春 said...

還是喜歡這裡-支持你..................................................................

7:10 AM  
Blogger 吳婷婷 said...

我們老得太快,卻聰明得太遲。..................................................

8:15 AM  
Blogger 吳婷婷 said...

相見亦無事,不來常思君..................................................................

8:07 PM  
Blogger SarahD_Mon淑盛 said...

要經常發表文章 最愛你了呦............................................................

2:20 AM  
Blogger 陳佑發 said...

很喜歡你的部落格,來給你加油,幫你推一下喔~期待你的下一個更新,謝謝............................................................

6:18 PM  
Blogger 家榮家榮 said...

感謝大大的無私分享 讓小弟獲益良多 終於知道網上還有這麼多的好東西≧▽≦"
............................................................

5:27 AM  
Blogger 琬安琬安 said...

安安唷~~幸運的日子送給妳(你)滿滿的幸福,也祝福你天天都開心唷...........................................................

1:40 AM  
Blogger 林聖瑤 said...

快樂,是享受工作過程的結果..................................................

10:23 PM  
Blogger 欣邱儀 said...

不簡單..一路走來辛苦了-............................................................

12:13 AM  
Blogger 王順蔡秉源如 said...

不論做什麼事,相信自己,別讓別人的一句話,把你擊倒。..................................................

2:36 AM  
Blogger 馨儿 said...

Everyone fastens where there is gain.............................................................

7:11 PM  
Blogger 邱思吳佳順翰 said...

活是一種鍛鍊靈魂的東西..................................................................

7:11 PM  
Blogger 蔡曼鄭美玉屏 said...

看到大家都留言-我也忍不住說聲---加油..................................................

2:22 AM  
Blogger 怡楊雯 said...

感謝你的分享 要繼續發表好文章喔..................................................

2:26 AM  
Blogger 恩宛玲如 said...

感覺很用心經營呢!鼓勵鼓勵............................................................

2:57 PM  
Blogger tongtong said...

傻氣的人喜歡給心 雖然每次都被笑了卻得到了別人的心..................................................................

5:59 PM  
Blogger 陳智雅威宜 said...

做些小善事,說些愛的字句,世界更快樂。..................................................

9:21 PM  
Blogger  said...

超棒的,給予最大支持。(* ̄△ ̄*)............................................................

2:44 AM  
Blogger 黃英吳思潔吳思潔邦 said...

你不能改變容貌~~但你可以展現笑容............................................................

6:32 PM  
Blogger gfjj said...

http://www.morikacopy.com/ ブランドコピー
http://www.morikacopy.com/louisvuitton/ ルイヴィトンコピー
http://www.morikacopy.com/chanel/ シャネルコピー

5:59 PM  

Post a Comment

Links to this post:

Create a Link

<< Home