| Author |
Topic |
|
newbietosql1221
Starting Member
25 Posts |
 Posted - 2010-10-06 : 19:25:46
|
| Can someone tell me how do i remove or prevent sql injectionI have a form written in (html and php) which does a search.Every time i enter a ' (single quote) i get a sql "Unclosed quote error"Is this an sql injection problem and how do i fix this problemHere is the sql code im running to do the search;$query="SELECT my_date, something, something FROM table_name WHERE something LIKE '%$stxt%' ORDER BY my_date DESC";I m using a _GET which i m thinking of using _POST |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-07 : 02:27:22
|
| "$query="SELECT my_date, something, something FROM table_name WHERE something LIKE '%$stxt%' ORDER BY my_date DESC";"Don't use "$stx" embedded in the string (I presume that inserts in the value of the [$stx] variable in PHP?You need to process [$stx] through a function first so that any embedded ' single quote will be doubled up to a pair of '' (that's two characters, not the double-quote character).MUCH MUCH MUCH better would be to convert the query to use parameters - better performance because query plans are cached, no risk of SQL injection (provided that the statement / stored procedure you call doesn't then, itself, do some sort of parameter mangling and dynamic SQL) |
 |
|
|
newbietosql1221
Starting Member
25 Posts |
Posted - 2010-10-07 : 09:19:57
|
| wow kristen thats way over my head, i have tried to put double quotes double quotes with single quotes... can you provide me with a sameple code of what im targeting? thanks |
|
|
|
newbietosql1221
Starting Member
25 Posts |
Posted - 2010-10-07 : 09:20:56
|
| im definitely learning a ton here, thanks all |
|
|
|
newbietosql1221
Starting Member
25 Posts |
Posted - 2010-10-07 : 09:22:09
|
| its just a search function, the $stxt is the form variable (search text box) thats it |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-07 : 10:28:42
|
| http://php.net/manual/en/function.str-replace.php |
|
|
|
newbietosql1221
Starting Member
25 Posts |
Posted - 2010-10-07 : 11:04:30
|
| I tried a to put it in a conditional statement if(isset $_POST['searchtext'] ) {$stxt = str_replace (" '" , " ") }that didnt work im doing something wrong or very stupid so please help |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-10-07 : 11:11:28
|
$query = "SELECT my_date, something, something FROM table_name WHERE something LIKE '%' + QUOTENAME('" + $stxt + "', '''') + '%' ORDER BY my_date DESC";
N 56°04'39.26"E 12°55'05.63" |
|
|
|
newbietosql1221
Starting Member
25 Posts |
Posted - 2010-10-07 : 11:18:22
|
| thx peso tried it but that didnt work eithergetting;Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '0'., SQL state 37000 in SQLExecDirect in |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-07 : 12:34:40
|
| $stxt = str_replace("'", "''", $stxt);$query="SELECT my_date, something, something FROM table_name WHERE something LIKE '%$stxt%' ORDER BY my_date DESC";Note that we are escaping single quotes by doubling them up -- 2 single quotes. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-07 : 13:19:20
|
| i know that it does, but i don't know how...not a php guy, but have to support many front end langauges hitting my dbs. |
|
|
|
newbietosql1221
Starting Member
25 Posts |
Posted - 2010-10-07 : 16:32:20
|
| it worked , so thanks allumm could there be a flaw somewhere else or something else result from using this, str_replace()? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-08 : 02:55:35
|
| No, but you must use it on EVERY form field / variable that you incorporate into a SQL Command String - even if they are (should be!) numbers unless you are sure that they are safe (e.g. generated by your program and not containing any data that came from a user).Parametrised queries would be MUCH better. (But its a big change for your program if your program is large; if your program is small then I strongly recommend that you read up and make the change now. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-08 : 04:58:30
|
quote:
Originally posted by Peso
$query = "SELECT my_date, something, something FROM table_name WHERE something LIKE '%' + QUOTENAME('" + $stxt + "', '''') + '%' ORDER BY my_date DESC";
I reckon that's too late isn't it?$stxt may still contain characters that causes the the SQL string to break? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-10-08 : 05:15:37
|
I haven't been able to break the QUOTENAME approach.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-10-08 : 05:54:13
|
quote:
Originally posted by Kristen
quote:
Originally posted by Peso
$query = "SELECT my_date, something, something FROM table_name WHERE something LIKE '%' + QUOTENAME('" + $stxt + "', '''') + '%' ORDER BY my_date DESC";
I reckon that's too late isn't it?$stxt may still contain characters that causes the the SQL string to break?
Also Quotename wont work if length exceeds 128. If parametrisation is not an option, another method is to use derived tablehttp://beyondrelational.com/blogs/madhivanan/archive/2010/05/14/derived-table-new-approach-to-avoid-sql-injection.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-08 : 06:57:41
|
quote:
Originally posted by Peso
I haven't been able to break the QUOTENAME approach.
Yeah, but this is in Application, no?$query = "SELECT my_date, something, something FROM table_name WHERE something LIKE '%' + QUOTENAME('" + $stxt + "', '''') + '%' ORDER BY my_date DESC";will break if $txt is');PRINT 'Hacked';--or somesuch, won't it? |
|
|
|
|
sql injection