Mod rewrite and sqlquery in urls

is it possible to seo urls with sql queries such as mine using mod rewirte

[phlogit]

Is it possible to change this url using modrewrite and if so how would it look like. wot params an stuff.

search.php?sqlquery=%20WHERE%201%3D1%20AND%20field3%3D%27%201%20search%20word%20%2B%20%27%20AND%20deleted%21%3D%271%27

if you can give some examples of what the url has potential to look like using mod rewrite.

All help appreciated thanx.

[whoisgregg]

Never allow users to directly provide SQL code. Read about SQL injection attacks [google.com].

One way to allow a mod_rewrite friendly search facility is with urls like this:

/search/some+query+here.html

Then rewrite it to the search file:

Options +FollowSymlinks
RewriteRule ^/search/(.*)\.html$ /search/index.php?query=$1 [L]

Then escape your input:

$sql = "SELECT * FROM `index` WHERE `keyword` LIKE '%".[b][url=http://php.net/mysql_real_escape_string]mysql_real_escape_string[/url][/b]($_GET['query'])."%' LIMIT 10";

[phlogit]

thanx for your reply so if i use mod rewrite like you said they wont be able to see the sql command in the address bar but will only see some+query+here.html and therefore no sql injections am i right?

[jdMorgan]

What they see in their address bar is determined by:

1) What they type into it.
2) Whatever appears because they clicked on a link on your page.

So, if you don't include the SQL query in the link, but instead provide a link in the format proposed above, and then use mod_rewrite to format the query, they will only ever see the link your provided.

An additional layer of security can be provided by using the pattern in the RewriteRule to only accept certain characters, such as a-z, A-Z, 0-9, hyphen, underscore, and perhaps a few others. For example, use ([a-zA-Z0-9_-]+) as the pattern, instead of (.*)

RewriteRule's [NC] flag can then be used to shorten the pattern by making it case-insensitive:

RewriteRule ^/search/([a-z0-9_-]+)\.html$ /search/index.php?query=$1 [NC,L]

Finally, an additional rule can be used to preven direct access to the "real" internal path used by the RewriteRule:

RewriteCond %{THE_REQUEST} ^[A-Z]{3,9}\ /search/index\.php\?query=
RewriteRule ^search/index\.php$ - [F]

With that in place, direct user access to index.php is impossible, it can only be reached using the "/search/<query>.html URL.

Jim

[Emsy]

Be very very careful with SQL queries when programming. Link the guy above said, you're vulnerable from SQL Injection attacks.

Don't forget to use PHP's mysql_real_escape_string() function to prevent SQL Injection attacks too!

[w3schools.com...]

[phlogit]

i use stripslashes does that do the same thing and help me prevent sql injections?

Hi jim - i do use the query in the link so therefore can modrewrite prevent the user from seeing the full query in the link.

for example;

search.php?query="where 1=1 etc"

use mod rewrite to make it look like this

search.html //or something as neat

Thanx guys for your help this is a sick forum!

[phlogit]

right i think after reading this thread
This sick thread [webmasterworld.com]

its safe to say that mysql_real_escape_string does what addslashes/stripslashes do + much more in escaping all the necessary characters.

am i right people?

[phlogit]

Just tried using mysql_real_escape_string in my query says the following.

Fatal error: Call to undefined function: mysql_real_escape_string()

any ideas what i am doing wrong?

[jatar_k]

>> am i right people?

yes you are right

but regardless, don't put queries in your urls or you'll quickly find someone feeding you an added inline query that deletes things

what version of php?
[php.net...]

> 4.3.0 is required

[phlogit]

Damn i got 4.2.3 looks like i need to upgrade. Thanx for letting me know jatar_k.

Regarding queries in urls whats the best way around it?

my search is all working i thought instead of changing the php itself i could use mod rewrite to mask or hideaway the queries in the url and make them either look like a .html file or not show at all is this possible?

so if i use the method whoisgregg showed to modrewrite my search url it should display as;

my+query+maybe+a+very+long+one.html

can people still do sql injections or feed an added inline query that deletes through this url?

[jdMorgan]

Very simply, the only way to change what people see in their browsers is by changing the links used on your pages. mod_rewrite can only change the browser address bar by redirecting the client, but by the time you;ve done that, the client has already captured the 'real' URL, so redirects are not a safe method for what you are trying to do.

You're being told that you cannot use SQL queries in URLs for security reasons. Therefore, you must design and use a set of 'fake and safe' URLs that support all the queries you need to do (and can securely allow; We don't know your site, so we can't tell you what these should be) and that do not contain any hint of an SQL query. You will link to these 'safe' URLs, and people will click them. When requests for these URLs arrive at your server, your mod_rewrite rules will be used to translate them into the proper form to call your script and do the relevant query. The SQL script and query is 'hidden behind,' and possible queries are constrained by, the safe URLs.

Each safe URL must contain all the information needed to build the query, but not necessarily in direct form. For example, you could use URL-parts like "/findpartno/<part-number-here>.html" and "search/<item-type-here>.html", and then map those inside the server to SQL lookups by part number and by multi-field search. By using 'safe' URL-parts like that, and recognizing only those safe URL-parts, you also prevent someone from successfully typing in "example.com/delete/*.html" or something like that, and having in translated by your "too-general" rewrite to a delete query...

This stuff isn't simple, and none of it is magic, but take it a step at a time, and you'll get through it... :)

Jim

[phlogit]

i understand what your saying jd but instead of changing my whole search method would it be easier for me to use mod_rewrite to just strip the query string from the url?

is this possible?

so that it just shows search.php

or something similar

[jdMorgan]

It would indeed be easier, but it won't work. The correct (and workable) solution is presented above.

Just as you use regular expressions in mod rewrite to "transform" a requested URL to a server filepath, you can use regular expressions in PHP (or PERL, etc.) to transform an old unsafe query URL into a search-engine-friendly and safe URL on your pages. This is a good approach if it is difficult or impossible to change the URLs produced by your back-end -- for example, if they are produced by a commercial, closed-source program.

The URL that appears on your pages *is* the URL. You can't change it after it appears on a page. That's what browsers will request, what search engines will list, and what hackers will attempt to exploit.

The filepath on the server *is* the real filepath. You can't change that except by renaming or symlinking a file or a script.

What you can change is the way that the server 'maps' requested URLs to server filepaths, and that is what mod_rewrite does. As mentioned before, it can also do redirects, but that is not an acceptable solution (security-wise) for the problem presented here.

Change the public/published/listed on-page URLs using, for example PHP's preg_match
Use mod_rewrite to send request for those URLs to the proper filepath.
The URLs on the Web are thus changed, while the internal server script operations are unchanged.

Jim

[phlogit]

Hi guys thanx for all your replies especially Jim. I've decided not to use my queries in the urls in order prevent sql injections. Before I was using header to redirect and carry part of the sql query. I have now scrap that method all together and instead of having my form submiting to submit.php and then carrying the results in the header to the page with the html looks and results called search.php. I put the two php files together and now my form submits to one php page where the query is done and results are displayed therefore nothing is carried in the header and the address bar only displays the file name e.g. submitsearch.php.

Have i gone for the right choice?

I have also decided not to use mysql_real_escape_string because magic quotes is on and therfore i just use stripslashes otherwise i would have used mysql_real_escape_string.

I am going to do validation both clientside (javascript) and serverside(php) in order to make sure no sql injections or any other attacks can be performed.

Thankyou very much guys for all your help you have been great.

ps. if you can answer the question above cheers.

[jdMorgan]

That will be a better approach.

Be sure that stripslashes will remove all but 'safe' characters from queries; Do not allow "%" and "<" etc., but only alphabetic, numeric, and 'normal' punctuation, and only as required for your search terms. In security-related matters, it is best to write the code to determine only what to accept; Don't try to write it to catch things you want to reject, as it is likely that you will miss something, and only discover it after you've been hacked...

As for client-side validation, use it only for 'user convenience' to inform them that the search box is blank, and simple stuff like that. Be aware that a malicious user won't enable JavaScript, so all critical validation and security-related 'clean-up' must take place on the server. Also, avoid the situation where the JS tells the user everything that you do and don't accept -- A normal user isn't going to be typing SQL-formatted queries into his/her borwser, and a malicious user could/will use such code to experiment and find out what exploits he can use.

Jim

[phlogit]

Yeh, thanx for the tip jim im using ereg as follows:

if(!ereg("^[A-Za-z0-9]{3,15}$",$username))
{
// invalid username
}

For all my form filling for the server side validation, only allowing characters i accept.

Hopefully this will be enough security to prevent hacking.