Webmaster Forum

Go Back   Webmaster Forum > Web Development > Coding Forum

Coding Forum Problems with your code? Discuss coding issues, including JavaScript, PHP & MySQL, HTML & CSS, Flash & ActionScript, and more.


Reply
 
Thread Tools Display Modes
Share |
  #1  
Old 10-11-2014, 02:39 PM
zen_web zen_web is offline
Contributing Member
 
Join Date: 03-16-11
Posts: 51
iTrader: 0 / 0%
SELECT query sanitization

I've been reading about sanitizing SELECT queries for mysql and php, but I'm more confused on what to do than when I started.

My question is simple: what should I do to sanitize SELECT queries to prevent attacks? Stored procs and parameterized data? Or something else?

I'm not taking any user input data in through forms. Just changing content based on variables in the URL string.
 
Reply With Quote

Advertisement

Advertisement

  #2  
Old 10-12-2014, 12:05 AM
arunthomas203 arunthomas203 is offline
Contributing Member
 
Join Date: 06-24-14
Posts: 68
iTrader: 0 / 0%
You can use php function urldecode

PHP Code:
$q urldecode($_GET['q']);
$sql "SELECT * FROM item WHERE title LIKE '%{$q}%'" 
 
Reply With Quote
  #3  
Old 10-13-2014, 08:04 AM
zen_web zen_web is offline
Contributing Member
 
Join Date: 03-16-11
Posts: 51
iTrader: 0 / 0%
Thanks for the replay arunthomas203.

A few questions:
1. In the PHP manual for urldecode it says that, the superglobals $_GET and $_REQUEST are already decoded, and decoding them again may cause unexpected behavior. Is it really advised to do a urldecode on the $_GET()?
2. Am I correct that this basically works in two parts? The two parts being:

a. Disabling any possible attacks by changing special characters into their %## representation. Essentially, changing the attack string to something that can't be executed by mySQL.
b. Using LIKE + %% in the mySQL string to only give results if it matches something that is actually in the database.

Am I correct in understanding how this approach works?

Thanks.
 
Reply With Quote
  #4  
Old 10-13-2014, 09:04 AM
J. H. Rasmussen J. H. Rasmussen is online now
Super Moderator
 
Join Date: 11-11-11
Location: Copenhagen, Denmark
Posts: 5,997
iTrader: 1 / 100%
You are correct with what you're saying in "b", however, not entirely correct in "a".

When you're are talking about "%##" then you're probably talking about URL encoding, when we talk about MySQL then what you need is basically to escape special characters(not encode them) there may be dangerous to use in a MySQL query.

I beleave now a days, mysqli_real_escape_string is what most webmasters use to escape those characters, before using a variable in in a MySQL query.

Here is an example from PHP's website:
Quote:
PHP Code:
<?php
$link 
mysqli_connect("localhost""my_user""my_password""world");

/* check connection */
if (mysqli_connect_errno()) {
    
printf("Connect failed: %s\n"mysqli_connect_error());
    exit();
}

mysqli_query($link"CREATE TEMPORARY TABLE myCity LIKE City");

$city "'s Hertogenbosch";

/* this query will fail, cause we didn't escape $city */
if (!mysqli_query($link"INSERT into myCity (Name) VALUES ('$city')")) {
    
printf("Error: %s\n"mysqli_sqlstate($link));
}

$city mysqli_real_escape_string($link$city);

/* this query with escaped $city will work */
if (mysqli_query($link"INSERT into myCity (Name) VALUES ('$city')")) {
    
printf("%d Row inserted.\n"mysqli_affected_rows($link));
}

mysqli_close($link);
?>
Source: http://dk1.php.net/manual/en/mysqli....ape-string.php
 
Reply With Quote
  #5  
Old 10-13-2014, 09:15 AM
zen_web zen_web is offline
Contributing Member
 
Join Date: 03-16-11
Posts: 51
iTrader: 0 / 0%
Thanks J. H. Rasmussen.

I forgot to ask the second part of my question. Will sanitization prevent SQL injection attacks? I am thinking it won't.

Will sanitizing GET data and used a stored proc prevent most attacks?
 
Reply With Quote
  #6  
Old 10-13-2014, 09:39 AM
J. H. Rasmussen J. H. Rasmussen is online now
Super Moderator
 
Join Date: 11-11-11
Location: Copenhagen, Denmark
Posts: 5,997
iTrader: 1 / 100%
It should prevent most MySQL attacks(if not all), remember to set the MySQL character set, before using mysqli_real_escape_string.

Whether sanitizing will work securely, does depend on what and how you sanitize input, but if you do it the right way, then you should have reasonable secure website.
 
Reply With Quote
  #7  
Old 10-13-2014, 10:16 AM
zen_web zen_web is offline
Contributing Member
 
Join Date: 03-16-11
Posts: 51
iTrader: 0 / 0%
Quote:
Originally Posted by J. H. Rasmussen View Post
It should prevent most MySQL attacks(if not all), remember to set the MySQL character set, before using mysqli_real_escape_string.

Whether sanitizing will work securely, does depend on what and how you sanitize input, but if you do it the right way, then you should have reasonable secure website.
Just to be clear, "It" in your first sentence is mysqli_real_escape_string and not any stored procs? You're saying I should get a reasonably secure website with mysqli_real_escape_string and without stored procs?

Sorry if it seems like I am not getting this. Just really tired today, and I want to make sure I don't make any mistakes.
 
Reply With Quote
  #8  
Old 10-13-2014, 03:56 PM
J. H. Rasmussen J. H. Rasmussen is online now
Super Moderator
 
Join Date: 11-11-11
Location: Copenhagen, Denmark
Posts: 5,997
iTrader: 1 / 100%
Yes, i was referring to mysqli_real_escape_string, when it comes to stored procedures, it's a very long time ago i last used that, so my knowledge about stored procedures are very limited, but here is a link from MySQL's website that may be helpful: Access Control for Stored Programs and Views
 
Reply With Quote
  #9  
Old 10-15-2014, 06:18 AM
zen_web zen_web is offline
Contributing Member
 
Join Date: 03-16-11
Posts: 51
iTrader: 0 / 0%
Thanks JH Rasmussen!
 
Reply With Quote
  #10  
Old 10-15-2014, 11:03 AM
brealmz's Avatar
brealmz brealmz is offline
Contributing Member
 
Join Date: 04-10-07
Posts: 90
iTrader: 0 / 0%
How about this?
$string = mb_convert_encoding($string, 'UTF-8', 'UTF-8');
$string = htmlentities($string, ENT_QUOTES, 'UTF-8');
$string =mysql_real_escape_string($string,$link);
 
Reply With Quote
  #11  
Old 12-13-2014, 10:56 PM
Milindji Milindji is offline
Junior Member
 
Join Date: 10-28-14
Location: Gurgaon
Posts: 40
iTrader: 0 / 0%
$q = urldecode($_GET['q']);
$sql = "SELECT * FROM item WHERE title LIKE '%{$q}%'"
 
Reply With Quote
  #12  
Old 02-08-2015, 07:02 PM
voodooKobra's Avatar
voodooKobra voodooKobra is offline
Contributing Member
 
Join Date: 09-04-13
Location: Florida
Posts: 79
iTrader: 0 / 0%
Quote:
Originally Posted by arunthomas203 View Post
You can use php function urldecode

PHP Code:
$q urldecode($_GET['q']);
$sql "SELECT * FROM item WHERE title LIKE '%{$q}%'" 
I really hope you are kidding.

Code:
http://yourdomain.com/yourscript.php?q=%27)%3B%20SELECT%20password%20FROM%20admins%3B--
GG.

Use PDO. Use prepare(), execute() and one or more of: fetch(), fetchAll(), fetchColumn().

https://github.com/resonantcore/lib/...lop/src/DB.php

I wrote this class to make it easier to get PDO's security without complicating your codebase.

PHP Code:
<?php
// Use an SPL autoloader or manually require the DB.php file here...

$db = new \Resonantcore\Lib\DB("mysql:host=localhost;dbname=test""username""password");

foreach (
$db->run(
    
"SELECT * FROM table WHERE name LIKE ? AND id < ? AND email != ?"
    
$_POST['name'] . '%',
    
1000,
    
'test@localhost'
) as $row) {
    
var_dump($row);
}

Last edited by voodooKobra; 02-08-2015 at 07:05 PM.
 
Reply With Quote
Go Back   Webmaster Forum > Web Development > Coding Forum

Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Which categorys would you select? Sprinkler Buddy Marketing Forum 5 05-09-2012 03:46 AM
Help me select Host bluffy Web Hosting Forum 25 06-19-2009 11:42 PM
Select * Query (good or bad) imaginemn Coding Forum 25 04-20-2005 08:03 PM


V7N Network
Get exposure! V7N I Love Photography V7N SEO Blog V7N Directory


All times are GMT -7. The time now is 05:40 PM.
Powered by vBulletin
Copyright 2000-2014 Jelsoft Enterprises Limited.
Copyright © 2003 - 2018 VIX-WomensForum LLC