Welcome! Log In Create A New Profile

Advanced

secure from SQL injection?

Posted by monksee 
secure from SQL injection?
February 04, 2015 05:23PM
Hi,

This is great.. Just managed to install it on my site. Thank you smiling smiley
I was wondering is this code secure from SQL injection when inserting into the database?

Regards,

Sarah
Tec
Re: secure from SQL injection?
February 04, 2015 05:38PM
http://xforce.iss.net/xforce/xfdb/95016

Tec
Re: secure from SQL injection?
February 04, 2015 05:49PM
Great thanks,

I might edit the admin page with parameterized queries if i can. thanks,

Sarah
t-p
Re: secure from SQL injection?
February 06, 2015 07:12PM
Hi TEC,
What's the solution for this vulnarbility?
Tec
Re: secure from SQL injection?
February 07, 2015 06:37PM
Most imoprtant and as Sarah already stated: you need to parametrize all queries.
In other words each query which is sent to the database needs to be escaped. At minimum. Special attention on all data which should be stored in db.

Beside database protection, all user input needs to be filtered (cleaned), so that script malfunction is prevented. Required also for all admin input, because the bad boys know the structure and subfolders of this search engine.
In order to protect the scripts and database also all variables handled from on script to another need to be checked for vulnerability attempts.
As an example:
…/admin/spider.php is running autonomously, supplied with all required parameter (variables) and started from
.../admin/admin.php

Depending on the type of intrusion attempt, different methods of defense are additionally required. In order to prevent XSS-attacks and Shell-executes, all bad words need to be eliminated.

In order to prevent bad SQL queries from outside, like
OR 1=1;--
need to be deleted. The above tries to start a query, which might offer username and password of db.

More difficult to prevent are strings like
%FF%FE%3C%73%63%72%69%70%74%3E
which is the tr/vb.hpq trojan. It could be used to control your complete database.

Suppress JavaScript execution and tag inclusions are 2 other tasks, required to protect your server environment.

Okay, you may call me paranoid, but during the last years I've seen very many and different attempts to intrude PHP scripts and MySQL databases. I am afraid you don't like to read the following. If you don't like to investigate a lot of time to harden your Sphider installation, use Sphider-plus, which contains all the above protections and some more
http://www.sphider-plus.eu/index.php?f=30#14
Just to be sure, I additionally implemented an 'Intrusion Detection System' (IDS). Depending on the impact of the intrusion attempt, the IDS prevents (blocks) further Internet traffic from URLs known to be evil.

Not sure whether this posting should remain here in this forum for a long time. Also, please do not ask me for any more details Too many bad boys, who might take this thread as a private lesson for their further . . .

Tec



Edited 1 time(s). Last edit at 02/07/2015 06:45PM by Tec.
t-p
Re: secure from SQL injection?
February 07, 2015 09:57PM
Thanks a lot TEC.
Appreciate you taking the time to explain.
You may delete the thread if you like.
Re: secure from SQL injection?
February 09, 2015 05:58PM
Hi Tec,

I also appreciate you explaining this further. and I don't mind if you would like to take the post down. I understand

Thanks a lot

Sarah
t-p
Re: secure from SQL injection?
February 09, 2015 11:46PM
@monksee,
if you (or anybody else) are able to harden this script, hope you can share it.
Re: secure from SQL injection?
February 10, 2015 02:53AM
As my next question is related to SQL parameterized queries I will post it in this discussion if thats ok.

I am changing the queries in the code to parameterized queries and testing the output as i go along to make sure i am writing them correctly...

Can you possibly give me an example of searchwords that would cause these two following loops to be executed within the search function so that I can test my queries:
my search will be based on only one site so im wondering will the following loop ever be executed? and also what are the phrase words referring to in the while loop down below?I hope I explained correctly. Thank you


//find all sites that should not be included in the result
if (count($searchstr['+']) == 0) {
return null;
}
$wordarray = $searchstr['-'];
$notlist = array();
$not_words = 0;
while ($not_words < count($wordarray)) {
if ($stem_words == 1) {
$searchword = addslashes(stem($wordarray[$not_words]));
} else {
$searchword = addslashes($wordarray[$not_words]);
}
$wordmd5 = substr(md5($searchword), 0, 1);

$query1 = "SELECT link_id from ".$mysql_table_prefix."link_keyword$wordmd5, ".$mysql_table_prefix."keywords where ".$mysql_table_prefix."link_keyword$wordmd5.keyword_id= ".$mysql_table_prefix."keywords.keyword_id and keyword='$searchword'";

$result = mysql_query($query1);

while ($row = mysql_fetch_row($result)) {
$notlist[$not_words]['id'][$row[0]] = 1;
//debug_to_console($row[0]);

}
$not_words++;
}


//find all sites containing the search phrase
$wordarray = $searchstr['+s'];
$phrase_words = 0;
while ($phrase_words < count($wordarray)) {

$searchword = addslashes($wordarray[$phrase_words]);
$query1 = "SELECT link_id from ".$mysql_table_prefix."links where fulltxt like '% $searchword%'";
echo mysql_error();
$result = mysql_query($query1);
$num_rows = mysql_num_rows($result);
if ($num_rows == 0) {
$possible_to_find = 0;
break;
}
while ($row = mysql_fetch_row($result)) {
$phraselist[$phrase_words]['id'][$row[0]] = 1;
}
$phrase_words++;

}
Re: secure from SQL injection?
February 11, 2015 04:12PM
It's ok actually I figured out when these are executed. when there is a search with a minus sign in it (to exclude a certain word) and when there is a search with quotes around it to search for an exact match i.e phrase.

I want to say thanks Tec for all the work you put in to this script and sharing it with us and the support you offer. I will be donating to the project.
@t-p I am just integrating a basic search to a template mobile website I have made.. I won't be hardening the whole script.
it will function to only search one website and no advanced options so it will be very basic. I can indeed show you how i have secured the queries within the functions I am using. I think as Tec said there is sphider-plus if you would like all of the necessary protection for the full sphider project. I hope that helps grinning smiley
Tec
Re: secure from SQL injection?
February 11, 2015 06:15PM
Hello Sarah,
<<< I will be donating to the project. >>>
Just to inform you: I am not the developer of the original Sphider. Just spent my time to develop Sphider-plus. In case you intend to donate to Ando Saabas, which is the developer of original Sphider, use the PayPal account ando(a t)set.ee
If like to donate to me, please use tec(a t)sphider-plus.eu
Thanks in advance.

Tec
Re: secure from SQL injection?
February 11, 2015 11:52PM
Thank you for the information Tec

I was wondering when you said this:
<<In order to protect the scripts and database also all variables handled from on script to another need to be checked for vulnerability attempts. >>
Do you mean the GET variables that are passed from page to page? I have used preg_match to make sure the $query string only contains letters, numbers white space and " and - ... Is this type of thing sufficient?

regards

sarah
Tec
Re: secure from SQL injection?
February 12, 2015 10:36AM
<<< Do you mean the GET variables that are passed from page to page? >>>
Yes I do. You will need to harden all scripts. Especally the indexing scripts, because they frequently store data into the database.

preg_match() is very important, but to protect the db, escaping all SQL queries in all scripts is essencial.

Please allow to me a final note and don't call me arrogant after reading it:
Meanwhile I investigated about 8 years to develop Sphider-plus. And in order to write open: I do not like to do back engineering for the original Sphider. Unsupported since 2008, there are too many bugs and limitations in these scripts. You may add some improvements like the preg_match() you suggested in your posting. But this is only part of hardening and will not prevent all kind of intrusions.
Think about all the time (hours) you will need to investigate into hardening all scripts, or alternately investigate once 25 Euro for Sphider-plus. Additionally participating on more than 300 new features (additional mods, functions, template designs and debugging), which I meanwhile added to the original Sphider. I do not ask for the 25 Euro because I want to buy a Ferrari next year. It is just to deter all the script kiddies, who are just playing around and wasting my time.

Tec
Sorry, only registered users may post in this forum.

Click here to login