Parameterized Queries

Question

I am currently learning parametrized queries as there are advantages to using them.

Could someone give some pointers by converting this block of code to a parametrized version?

Thanks.

if(isset($_GET['news_art_id']) && (!empty($_GET['news_art_id'])))
{
    $news_art_id = htmlentities(strip_tags($_GET['news_art_id']));
    $news_art_id = validate_intval($news_art_id);

    //echo $news_art_id;
    $_SESSION['news_art_id'] = $news_art_id;

    // Assign value to status.
    $onstatus = 1;
    settype($onstatus, 'integer');

    $query = 'SELECT M.id, M.j_surname, M.j_points_count, M.j_level, A.j_user_id,A.id, A.jart_title, A.jart_tags, A.jart_description, A.jart_createddate FROM jt_articles A, jt_members M WHERE M.id = A.j_user_id AND A.id = ' . check_db_query_id($news_art_id) . " AND A.jart_status = $onstatus;";

    $result = mysql_query($query) or die('Something went wrong. ' . mysql_error());  
    $artrows = mysql_num_rows($result);
}

Instead of using htmlentities(), strip_tags(), and your "validate_intval()" function, why not just do: $news_art_id = (int)$_GET['news_art_id']; (int) will transform any non-integer value into "0" (which is usually not a valid i.d. and will match nothing in your db). No chance of SQL injection that way and it uses a lot less code. — Buttle Butkus
– Buttle Butkus, Commented May 11, 2012 at 4:05

Smi · Accepted Answer · 2012-10-26 14:55:07Z

3

The general rule is: Every variable should be binded, no inline variables at all.

Technical details: http://php.net/manual/en/pdo.prepare.php

edited Oct 26, 2012 at 14:55

Smi

14.4k9 gold badges61 silver badges66 bronze badges

answered Dec 16, 2010 at 14:37

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Frank Nwoko Over a year ago

A real life example using the above code block will really go a long way in explaining to some of us learning how to implement it. For example how one gat variables from form values?? Thanx

zerkms Over a year ago

@Frank Nwoko: please, read that article and try to do it yourself. After that, if you'll experience some issues - come back here and ask some specific question. That is the correct way to learn.

Jon Black · Accepted Answer · 2010-12-16 15:35:22Z

0

in your case there is no advantage, remember a parameterised query requires 2 calls to the db : one to setup the query template and parse, the other to populate the query template params and is typically used when looping. So in this instance you're better off calling a stored procedure (always the best choice) or using inline sql and making sure you use http://php.net/manual/en/function.mysql-real-escape-string.php when applicable.

edited Dec 16, 2010 at 15:35

answered Dec 16, 2010 at 15:03

Jon Black

16.6k5 gold badges45 silver badges42 bronze badges

Collectives™ on Stack Overflow

Parameterized Queries

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related