Sql injection

[alibaba]

i know this subject discussed before
but cant find now
i have website with search box
my concern is sql injection through search string

so wht can be done to prevent sql injection

thnks in advance

 

[Ray]

Im sorry, i dont understand your question?

What i got is you have a search box that logs seach strings?

 

[alibaba]

yes i have search box and i m afraid of sql injection
i guess there is kinda stripping function for malicious attacks

 

[Peter]

run the search string through mysql_real_escape_string before carrying out the query and it will make the data safe (as long as you are using MySQL of course).

Before you run it through this function you should check if magic_quotes_gpc is enabled if it is run the search string through stripslashes.

 

[alibaba]

run the search string through mysql_real_escape_string before carrying out the query and it will make the data safe (as long as you are using MySQL of course).

Before you run it through this function you should check if magic_quotes_gpc is enabled if it is run the search string through stripslashes.

thanks i will try

 

[Dubzoo]

i think a good idea would be to validate your form input so no one could perform a query....???? :blink: :blink:

 

[psalzmann]

Proper filtering and removal of certain characters is a start. You cannot "fix" sql injection by running some function like mysql_real_escape_string() because it't not good enough.

You will need to write your code with your own filters. For example, if you're allowing someone to enter their phone number, you'd use a filter for only numbers.

-Peter

 

[Peter]

Proper filtering and removal of certain characters is a start. You cannot "fix" sql injection by running some function like mysql_real_escape_string() because it't not good enough.

Ok please explain why mysql_real_escape_string() is not good enough? this function takes into consideration the charset currently used by the mysql connection and also escapes all characters that have special meaning in mysql.

You will need to write your code with your own filters. For example, if you're allowing someone to enter their phone number, you'd use a filter for only numbers.

If at all possible then by all means do that but it is not always possible.

 

[psalzmann]

Ok please explain why mysql_real_escape_string() is not good enough?

It's great but not by itself.

What happens when a person uses this:

';alert(String.fromCharCode(88,83,84))//\';alert(String.fromCharCode(88,83,85))//";alert(String.fromCharCode(88,83,86))//\";alert(String.fromCharCode(88,83,87))//--></SCRIPT>">'><SCRIPT>alert(String.fromCharCode(88,83,88))</SCRIPT>

Here is the same line of code, just urlencoded:

%27%3Balert%28String.fromCharCode%2888%2C83%2C84%29%29%2F%2F%5C%27%3Balert%28String.fromCharCode%2888%2C83%2C85%29%29%2F%2F%22%3Balert%28String.fromCharCode%2888%2C83%2C86%29%29%2F%2F%5C%22%3Balert%28String.fromCharCode%2888%2C83%2C87%29%29%2F%2F--%3E%3C%2FSCRIPT%3E%22%3E%27%3E%3CSCRIPT%3Ealert%28String.fromCharCode%2888%2C83%2C88%29%29%3C%2FSCRIPT%3E

Keep in mind: NamePros is using VB. VB splits "long-single-string" code with a space after x characters. Look above: At the end of the 2nd line above, %2FSCRI PT%3E .. see the space between I and P ? Also, there's another one before this ".fromCh arCode%" h and a .. a space! Really, it just prevents the post from no-wrap and having you scroll to the right to see the rest of it as one long line (cosmetics really).

So watch this if you're testing this on your own code. Just a safety guard for NP community and long strings. So when you test make sure there are no spaces (to really try this on your code).

Both should work if you're not properly filtering keywords used to search or save information to the db. Again, *_real_escape_string() doesn't work for that above. It'll just store it in the db, and when it's time to present itself, will be when the hack takes place. What is more, it may even show a javascript prompt alert which reads "XSS" (if you're just searching via keywords for example). That's what this example above does. 3 ways to try and ouput a javascript alert prompt (for those wondering what it is).

This code could be considered a Trojan (since it executes at a later date, sitting in your db waiting for the perfect time to present itself -- an article, a date, a username, etc) .. and it's just an example for the sake of this topic.

So, if your coding techniques are tight, the above examples should not work.

Take care,

-Peter

 

[Peter]

You have missed the point completely psalzmann mysql_real_escape_string is not meant to stop xss it is purely there to stop sql injection which it does do.

The best way to stop xss where you are unable to filter out unwanted characters (for example in a database like this where the user can enter free form text) you should run the output through htmlspecialchars.

However this detracts from the original post. The OP asked how to stop sql injection specifically.

 

[alibaba]

you guys were helpful
thnks

 

[codyBane]

First I have to second a few things that have been mentioned.

1) use mysql_real_escape_string - not just on variables that are inputted by the user - you never know when a variable could have been over written by some grey or black hat means you didn't intend.

2) mysql_real_escape_string is not enough. Filter your input - do not sit there and try to remove or escape all the potentially bad characters. Simply remove all characters except those which are allowed and expected for that input field. preg_replace and a few other php functions are great for this.

3) in fact - use mysqli functions, not mysql functions. Why, see this link below:
http://simon.net.nz/articles/protecting-mysql-sql-injection-attacks-using-php/

This is an exceptional article covering sql injection starting with some common poor protection techniques, explaining why they don't work, moving on to explain what helps about mysql_real_escape_string and finally explaining why to bother using the mysqli functions that php5 provides us.

Cheers

 

[jackio]

You can find a good class to protect you against nasty attacks. PHP Input filter class. Available here - http://www.phpclasses.org/browse/package/2189.html

 

[Ik]

Just a question everyone,

Regardless the DB you are using, MySQL, SQL, Oracle, etc... Isn't using stored procedures help preventing SQL-Injection?

Thanks

 

[jackio]

Ik: It's also possible to sql inject on stored procedures and you can prevent it the same way as normal querys.

 

[Ik]

Ik: It's also possible to sql inject on stored procedures and you can prevent it the same way as normal querys.

Thanks for the info, jackio

 

[Peter]

Prepared statements are used to help against sql injection. In fact if you are using mysqli it is usually the preferred method.

Regardless however you should always validate the data.

 

[unknowngiver]

ah exactly what i was looking for
just wondering...if i use the htmlspecialchars function to display the output would it get rid of stuff "www." or "http://"
for a link directory, where a user submits "www.site.com" or "http://www.site.com" would the htmlspecialchars function work?
thanks

 

[zacerwin]

thanks a lot I was wondering same things!