Can an Equal sign be passed in RESTful?

Question

My RESTful interface works fine until I try to pass a WHERE statement

Example:

perimeters that are passed:

SELECT = "this";
FROM = "that";
WHERE = " 'ID' = 332";

the URL might look like this

www.example.com/rest.php?SELECT=this&FROM=that&WHERE='ID'=332

then in my php script

if (isset($_GET['SELECT']))
{
    $SELECT = $_GET['SELECT'];
}
if (isset($_GET['FROM'])) 
{
    $FROM = $_GET['FROM'];
}
if (isset($_GET['WHERE'])) 
{
    $WHERE = $_GET['WHERE'];
}

So Im thinking that the equals sign in the WHERE statement is messing it up. Would I be correct in this statement?

And if so what might be an alternative?

http://php.net/rawurlencode - **everything** you put in the url should be properly encoded — zerkms, Aug 27 '12 at 00:52
Passing SQL as a param seem like the wrong solution and opening yourself up to SQL injection or accidental VERY HEAVY Queries. Can you not pass specific parameters and build the SQL yourself on the server? — scunliffe, Aug 27 '12 at 00:54
@scunliffe: +1. The only exception would be a web-based database administration tool. But you'd not be writing those yourself. Anything you write for your app should not accept SQL fragments. — Thilo, Aug 27 '12 at 00:58
Having everything go through a single `rest.php` with SQL query parts of parameters is pretty much the epitome of **not** RESTful. — ceejayoz, Aug 27 '12 at 01:13
there cant be injection script because its only set up to use SELECT. Although heavy queries i havnt thought of. At the same time, these queries are only coming from an internal application. they are not for the public. — MichaelTaylor3D, Aug 27 '12 at 01:14
You need to go learn about SQL injection before claiming this isn't vulnerable to it. Chances are all I have to do is send a `WHERE` of `'ID' = 332; DELETE * FROM table;` — ceejayoz, Aug 27 '12 at 01:16
@ceejayoz even though Im using mysql_realescapestring on the php side? — MichaelTaylor3D, Aug 27 '12 at 01:22
You can't possibly be based on the question as asked, if you want stuff like WHERE = `'ID' = 332` to work. The quotes around `'ID'` would be escaped. — ceejayoz, Aug 27 '12 at 01:27
@ceejayoz true, i havnt implemented it yet, but it was on my to do list. I hadnt thought of the quotes though. Im going to try to rewrite the php with the new information I just learned from this question. — MichaelTaylor3D, Aug 27 '12 at 01:31
Hi! This is me from the future! Wow! .... I was pretty dumb back then hahahahaha — MichaelTaylor3D, Apr 11 '18 at 17:37

bryanmac · Accepted Answer · 2012-08-27T01:13:22.510

4

RESTful interfaces are about resources that are permalinks. Exposing direct queries over your database does not encapsulate your storage layer and is not RESTful. It's a transparent RPC mechanism over http to query your database (directly by the consumer).

In your example:

www.example.com/rest.php?SELECT=this&FROM=that&WHERE='ID'=332

'that' seems to be the resource, 'this' is the data on it and id is the unique reference to that object.

So, to be more restful, consider:

www.example.com/api/that/{id}

The data returned contains the 'columns' or attributes

{
    attr1:val1,
    attr2:val2
}

Your server can map that/{id} to a method which takes the id, formulates the necessary sql query, get's the tabular data, populates an objects and returns it (serializing to json, xml, etc...)

If you need to further filter, consider querystrng params to control the options (but not a sql where clause).

www.example.com/api/that/{id}?option=val1&option2=val2

edited Aug 27 '12 at 01:13

answered Aug 27 '12 at 01:08

bryanmac

38,941
11
91
99

How would the perimeters be extracted using $_GET. I do like this format much better if I can figure out how to properly parse in on the php side to build my query – MichaelTaylor3D Aug 27 '12 at 01:19
I've done all my REST servers in node.js/express and C# (asp.net webapi). Both of those offer easy ways to map methods based on routes. Haven't done it in php and I'm there's some library. A quick google shows some using $ _ SERVER['PATH_INFO'] which would return /api/that/{id} – bryanmac Aug 27 '12 at 01:23
Another google for "php rest routes" gave back : http://stackoverflow.com/questions/5665508/php-rest-api-routing – bryanmac Aug 27 '12 at 01:24
perfect! Im glad I decided to ask this question – MichaelTaylor3D Aug 27 '12 at 01:25
2

great read: http://blog.steveklabnik.com/posts/2011-07-03-nobody-understands-rest-or-http – bryanmac Aug 27 '12 at 01:27
1

Note that the [OData](http://www.odata.org/) protocol allows to pass queries in a (almost) SQL like syntax: http://www.odata.org/documentation/uri-conventions#QueryStringOptions – William Durand Aug 27 '12 at 15:03
@bryanmac I completely rewrote my php today, based on all the feedback i got from this question. It checks for any keywords such as DELETE, DROP TABLE, and ' OR ' To try to block injection script. I also blocked "*" to block against accidental heavy queries. I then used the format you suggested which really makes it easier at parse and makes a lot of sense and cleaner code. – MichaelTaylor3D Aug 28 '12 at 02:43
The only thing I found out that I cant do (for specific reasons) is that I cant map that/{id} to a method in my php. So an example URL might look like this: www.example.com/api.php/that/{ID}?option=this I was wondering if that last part (the mapped routes) was an essential part of the security of it? And if you might suggest any other security issues I should implement – MichaelTaylor3D Aug 28 '12 at 02:44
1

Routes which map url patterns to methods is a programming convenience. The key for security is that the user isn't passing sql which you attempt to validate and then execute directly. Make sure you're constructing the sql based in inputs (the id in this case and a couple options). When you build the sql make sure sql parameters and don't concat a string. – bryanmac Aug 28 '12 at 11:46

Can an Equal sign be passed in RESTful?

1 Answers1