I know sc_sql_injection() is for our hand coded sql statements, but when is sc_sql_injection() necessary? It appears, sc_sql_injection() places single quotes around non-text values which causes the sql where clause to fail.
Is it only to be used when an assignment value is text and when the where clause is on a text field?
Ed
The only place I’ve seen it come up is in the login application of the security module, on the OnValidate event.
In that app, you have the classic login form with two fields: {login} and {pswd}. When you submit the form, the onValidate event does this first thing:
$slogin = sc_sql_injection({login});
$spswd = sc_sql_injection(md5({pswd}));
Later on the code, the variables $slogin and $spswd are used in a SQL command:
$sql = "SELECT
active,
name,
email
FROM z_users
WHERE login = $slogin
AND pswd = ".$spswd;
sc_lookup(rs, $sql);
Perhaps you are using something similar on your where statements, but leaving the quotes out of the variables (like WHERE login = ‘$slogin’ AND pswd = ‘$spswd’; )
In theory you must always prevent the sql injection, no matter if the input is a text or another type (even if the UI blocks an input as text or number or any other type, the POST or GET call to the server can be modified in the client side…)
emilner sc_sql_injection does a lil bit more than that. If you remember, when someone is trying to inject SQL code into your app would do something like
Field: [random value'; Select * from Fake where '' =' ]
As you can see you they are terminating the string value so when you use such value in a query a subquery is triggered:
$query = "Select * from mytable where column= '". {field} . "'"
//using the example above the query would be somehting like
Select * from mytable where column='random value'; select * from Fake where '' =''
in this case im using a select to a nonexistent table, but someone with high SQL skills, might get way more information including but not limited to:
sc_sql_injection will manipulate such string in order to be considered a single string to the resulting query looks like
$query = "Select * from mytable where column= '". {field} . "'"
//using the example above the query would be somehting like
Select * from mytable where column='random value\'; select * from Fake where \'\' =\'' <- this is a valid string not truncated by the semicolons nor the quotes
My recomendation… use it whenever you use the value of a field directly into an SQL statement. If I remember well by default the generated SC fields have an injection validation already but when you pass global vars and stuff you should always use an injection valudation using this macro.
Hope this helps.
Regards
How fortuitous…I as just fiddling around with this.
I have a blank app to return json based on a GET call with parameters like …?fields=id,name&filter=name+like+%27%25this%25%27 (being the url-encoded form).
This is used in an sc_lookup statement with string “SELECT $fields FROM mytable WHERE $filter”.
Originally I used sc_ to sanitize the $_GET globals into $fields and $filter but the app never worked. It never could recognize the values. So I ended up removing sc_sql_injection and it worked immediately.
Was this a superfluous use of sc_sql_injection and if not how else can this be made safe?
[QUOTE=kafecadm;38376]emilner sc_sql_injection does a lil bit more than that. If you remember, when someone is trying to inject SQL code into your app would do something like
Field: [random value'; Select * from Fake where '' =' ]
As you can see you they are terminating the string value so when you use such value in a query a subquery is triggered:
$query = "Select * from mytable where column= '". {field} . "'"
//using the example above the query would be somehting like
Select * from mytable where column='random value'; select * from Fake where '' =''
in this case im using a select to a nonexistent table, but someone with high SQL skills, might get way more information including but not limited to:
sc_sql_injection will manipulate such string in order to be considered a single string to the resulting query looks like
$query = "Select * from mytable where column= '". {field} . "'"
//using the example above the query would be somehting like
Select * from mytable where column='random value\'; select * from Fake where \'\' =\'' <- this is a valid string not truncated by the semicolons nor the quotes
My recomendation… use it whenever you use the value of a field directly into an SQL statement. If I remember well by default the generated SC fields have an injection validation already but when you pass global vars and stuff you should always use an injection valudation using this macro.
Hope this helps.
Regards[/QUOTE]
[QUOTE=scriptcaser;38381]How fortuitous…I as just fiddling around with this.
I have a blank app to return json based on a GET call with parameters like …?fields=id,name&filter=name+like+%27%25this%25%27 (being the url-encoded form).
This is used in an sc_lookup statement with string “SELECT $fields FROM mytable WHERE $filter”.
Originally I used sc_ to sanitize the $_GET globals into $fields and $filter but the app never worked. It never could recognize the values. So I ended up removing sc_sql_injection and it worked immediately.
Was this a superfluous use of sc_sql_injection and if not how else can this be made safe?[/QUOTE]
I thiknk your problem was more of how you used it… if you try the same, instead of executing the sql query simply show it on screen, and you will c what i mean.
Regards