mySQL failed attempt at getting posts from last 24hr, and last 60 min

Question

Ok Editing this...

SELECT *
FROM votelog
WHERE ipaddress = '127.0.0.1'
AND datevoted
BETWEEN DATE_SUB( CURDATE( ) , INTERVAL 24 HOUR )
AND CURDATE( )
LIMIT 0 , 30

That is an example of the query I am attempting to run to find posts within the past 24 hours. I am also running a separate one for different needs for in the past 60 minutes. Issue is there is at least 4 rows in the table I am testing with 3 of which fall under the 24 hour clause.

Edit

Ok so I figured out my problem, 1 Im to damn tired.. 2 Horrible use of Between and Date_Sub.. It didn't dawn on me till now I should have been using the col name where I have CURDATE() going to answer my own question below.

this is what the timestamp in the DB looks like, standard DATETIME.. 2011-09-01 13:20:08

with that being said I am yielding no results.

I would use PDO (or MySQLi) to **prepare** the statements, it usually makes them more readable (unrelated to answer, but still good to know) — Madara's Ghost, Sep 03 '11 at 10:19
Could you **please** format your code to make it **readable** by others? THANKS — Your Common Sense, Sep 03 '11 at 10:19
And also read something reliable on preventing SQL injections. — Your Common Sense, Sep 03 '11 at 10:32
@Col, based on your comments, and lack of support backing up what your saying other than for telling me I need to go read a book or something. I am thinking you really need to read a book, a couple sites, etc.. about coding yourself. Don't assume off of one line of code that my code wrapping those queries doesn't take measures to prevent injection. You could also assume I am on a development machine not a production one.. But, if your not going to be of any help with anything why annoy people with petty comments that you back nothing up with? — chris, Sep 03 '11 at 10:44
No need for excuses. I am not accusing you :) I just tried to help. But you're free to act whatever you want. And leave this injection intact :) — Your Common Sense, Sep 03 '11 at 11:37
The injection Col. is talking about is the fact that `mysql_real_escape_string()` provides protection only if the escaped value is wrapped in quotes inside the query. Otherwise, it is completely useless. Example: `and 1=1` — Pekka, Sep 03 '11 at 13:06

score 3 · Accepted Answer · answered Sep 03 '11 at 10:22

3

You are not using BETWEEN correctly, the correct syntax is:

expr BETWEEN min AND max

you should change the end of your query to:

...BETWEEN DATE_SUB(CURDATE(), INTERVAL 24 hour) AND CURDATE()

or use > operator.

answered Sep 03 '11 at 10:22

nobody

10,599
4
26
43

oops! I didn't notice curdate(). this function would fail – Your Common Sense Sep 03 '11 at 10:54

xdazz · Answer 2 · 2011-09-03T13:05:02.300

1

$query = "SELECT * FROM votelog WHERE ID=".(int)$_GET['id']." AND ipaddress='".mysql_real_escape_string(getRealIpAddr())."' AND datevoted > DATE_SUB(CURDATE(), INTERVAL 24 hour)";

Edited: Since ID is not string type, instead of mysql_real_escape_string($_GET['id']), just use (int)$_GET['id'].

edited Sep 03 '11 at 13:05

answered Sep 03 '11 at 10:20

xdazz

158,678
38
247
274

3

@Col Where is the SQL injection? – xdazz Sep 03 '11 at 10:28
3

@Col Elaborate instead of just pointing out what you find obvious? – Repox Sep 03 '11 at 10:32
@Repox don't you see it yourself? Am I only one here who understand sql injections? – Your Common Sense Sep 03 '11 at 10:34
1

@Col Haha..., Do you mean when there is not a mysql connection? I just copied the OP's code and make some edit for this question. SQL injection is another question. – xdazz Sep 03 '11 at 10:34
no, there is an injection in the very code you posted. And a situation when everyone thinks they understand SQL injections while noone really have a clue - is indeed another question – Your Common Sense Sep 03 '11 at 10:36
its not injection, its a get variable.. usually frowned upon within a query for the possibilities of injection.. maybe you should read a book or 2 about injection prevention rather than preaching on about how its there, not knowing what or where the rest of the code wrapping it is doing to maybe prevent injection like you so humbly say I should concern myself about by flooding the comments here. – chris Sep 03 '11 at 10:38
1

a classic example - `id=1 or 1=1` – Your Common Sense Sep 03 '11 at 10:40
ummm. is this example looks unfamiliar to you? well, apply any other working example you know. Anyway I see no point in your question. Does it matter if i got it from google or off my head? – Your Common Sense Sep 03 '11 at 10:52
2

@Col Unless you're implying that the only SQL injections safe code is using prepared statements, I fail to see why the code is so unsafe as you imply. Instead of just pasting some generic SQL injection attemps found on the web, elaborate how you would exploit the code in the answer with SQL injection. And just answering it should be obvious doesn't make an answer. My guess? You don't have a clue and just wanted something smart to say. – Repox Sep 03 '11 at 10:56
@Repox no, I am talking not of prepared statements but of this very code, which is vulnerable. If you can't see it - just run this code with an example data I provided. Running codes always helps. I am doing it every time I fail to see something by peering into code. Just change the expression to `id=1 and 1=0` to make results more visual - it won't return any rows – Your Common Sense Sep 03 '11 at 11:01
@Col So, inserting something in the select statement that the database can't find is suddenly a SQL injection attack, just because no results are returned? So you weren't talking about inserting data, selecting something else than expected or deleting data through SQL injection? Guess the code is safe enough after all... – Repox Sep 03 '11 at 11:08
@Repox well, insert any injection of your own, if you don't like my example – Your Common Sense Sep 03 '11 at 11:09
@Col I don't have too - I'm not the one claiming that the code is vulnerable to SQL injections. Besides, other that getting a MySQL error from the escaped input, if it's not convertable to an integer, I dont' see what harm you might be able to do. – Repox Sep 03 '11 at 11:12
@Repox I am not insists. It's up to you. There was a chance for you to learn something, but I have no intention to force you or whatever. You are free to remain in the same state. But what error you're talking about? – Your Common Sense Sep 03 '11 at 11:15
@Repox what the hell are you guys talking about? It is possible to inject arbitrary SQL into this code. That's, like, the classical definition of SQL injection. – Pekka Sep 03 '11 at 12:52

score 1 · Answer 3 · answered Sep 03 '11 at 10:24

1

Have you tried this? The BETWEEN needs an AND you know...

$query = "SELECT * FROM votelog WHERE ID=".mysql_real_escape_string($_GET['id'])." AND ipaddress='".mysql_real_escape_string(getRealIpAddr())."' AND datevoted BETWEEN DATE_SUB(CURDATE(), INTERVAL 24 HOUR) AND DATE_SUB(CURDATE(), INTERVAL 60 MINUTE)";

And I think you could always do it like this

 $query = "SELECT * FROM votelog WHERE ID=".mysql_real_escape_string($_GET['id'])." AND ipaddress='".mysql_real_escape_string(getRealIpAddr())."' AND datevoted >= DATE_SUB(CURDATE(), INTERVAL 24 HOUR) AND datevoted <= DATE_SUB(CURDATE(), INTERVAL 60 MINUTE)";

answered Sep 03 '11 at 10:24

Kemal Fadillah

9,760
3
45
63

both queries are for different reasons, but both were throwing error. But thank you. – chris Sep 03 '11 at 10:28
assume to quickly that the code is as simple as it looks and injection is inevitable. One this is a dev machine, its all about testing concepts so injection isn't a worry. second, even if it did contain injectable code it would have to make it past all the other measures first that layer over the queries to help prevent the injection notion in the first place :-) @Kemal, some type of syntax error, wasn't displaying much to work with at least anything relevant. – chris Sep 03 '11 at 10:34
1

@chris can you tell me the what the syntax error message exactly is? Perhaps I could help... – Kemal Fadillah Sep 03 '11 at 10:40
not running into it anymore.. Just specified check my syntax. which boiled down to how I was using the between without the this and that mannerism. – chris Sep 03 '11 at 10:47
This code is vulnerable to SQL injection despite the use of `mysql_real_escape_string()`. – Pekka Sep 03 '11 at 12:54

score 0 · Answer 4 · answered Sep 03 '11 at 12:16

0

SELECT *
FROM votelog
WHERE ipaddress = '127.0.0.1'
AND 
DATE_SUB( datevoted , INTERVAL 24 HOUR )
LIMIT 0 , 30

So after much staring this is my result... i feel dirty now..

answered Sep 03 '11 at 12:16

chris

36,115
52
143
252

score 0 · Answer 5 · edited May 23 '17 at 11:55

You probably don't have a date but a datetime field (or a timestamp, which is slightly different). Don't use CURDATE(). In your query, CURDATE() gives 2011-09-03 (a date!) and when it is compared to your datetime field it is treated as 2011-09-03 00:00:00 (midnight!), so your query (if run today) same as:

SELECT *
FROM votelog
WHERE ipaddress = '127.0.0.1'
AND datevoted
BETWEEN DATE_SUB( `2011-09-03 00:00:00` , INTERVAL 24 HOUR )
AND `2011-09-03 00:00:00`
LIMIT 0 , 30

so, same as:

SELECT *
FROM votelog
WHERE ipaddress = '127.0.0.1'
AND datevoted
BETWEEN `2011-09-02 00:00:00` AND `2011-09-03 00:00:00`
LIMIT 0 , 30

That's why you lose any records that are between passed midnight and now.

Use NOW() :

SELECT *
FROM votelog
WHERE ipaddress = '127.0.0.1'
  AND datevoted
      BETWEEN DATE_SUB( NOW( ) , INTERVAL 24 HOUR )
          AND NOW( )
LIMIT 0 , 30

Useful readings:

MySQL docs: The DATETIME, DATE, and TIMESTAMP Types

MySQL docs: Date and Time functions

SO question: datetime vs timestamp