Solved

best way to sanitize user input for PDO queries

Posted on 2014-03-07
8
1,013 Views
Last Modified: 2014-03-07
Hi,

im curious what is the best way to sanitize user inputs when running PDO sql queries

im used to using mysqli procedural style where i just escaped all my variables and used only self created variables. any advice is greatly appreciated
0
Comment
Question by:M. Jayme Nagy
  • 3
  • 3
  • 2
8 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 39913632
You don't need to sanitize anything on insert.
Maybe someone adds some HTML etc to their input and you want to remove it then you could use regex to remove it.

preg_replace('/<[^>]*>/', '', $input)

But only if it shouldn't be there.

You could just leave everything as is and then escape anything untoward when you send it to the browser.
0
 
LVL 6

Author Comment

by:M. Jayme Nagy
ID: 39913651
I actually like having the ' " if the user would like to write i'll so id i do not have to user regex im liking the new technology. however as i was aware of the security vulnerabilities with mysqli im not with pdo so im trying to do some research


when i sent the variables to the prepared statement in the SQL server i dont need to clean them? and if so how would i go about it

for example could i just send $_POST['user_input']
0
 
LVL 58

Accepted Solution

by:
Gary earned 250 total points
ID: 39913682
Not sure what you mean I actually like having the ' " if the user...
It doesn't remove apostrophes etc if that's what you mean, for example say someone entered this for a name input
<b>John</b>
The regex above would remove the BOLD tags (or any html tags) so you would end up inserting into the db just
John

The basic premise is
$stmt = $conn->prepare('insert into mytable (mycol) values (:myval)');
$stmt->execute(array( ':myval' => $_POST['user_input']  ));

Open in new window

You don't need to do anything else to the variables, they cannot do anything nefarious to your database or screw up your sql statement.
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 39913688
All external data is by definition tainted.  Start with that premise, then begin to deconstruct it into things that are appropriate for your applications.

Do you expect a number in a request variable?  Check to see if it is a number.  Are you looking for a person's name?  See if it has a reasonable length and a reasonable character set  Do you expect a ZIP code?  It needs to be five integers or nine integers, but some people will write 22101-3104 so a reasonable test might be to eliminate the non-integer characters then test the length for five or nine.  Do you expect a USA telephone number?  Make sure the second digit it not 9.  Etc.  Are you looking for input from a human instead of a 'bot?  Check the CAPTCHA.

There is no one formula for everything, just common sense.  Dealing with quote marks like in the name O'Brien is just the very tip of the iceberg.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Author Comment

by:M. Jayme Nagy
ID: 39913691
I GOTCHA !!!

i dont have to do anything special other than testing the data is what i want as the statement is already send im just filling in the blanks

is this correct?
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 39913696
Sidebar note about HTML when it's not expected... PHP has a function for that.
http://php.net/manual/en/function.strip-tags.php

But what would you have the script do if it detected that someone was putting in malicious JavaScript?  Would you store it or reject it?  When you echo any text to the browser, you really, really want to use htmlentities() or similar.  Some further good reading on this sort of thing is available here:
http://php.net/manual/en/security.php
0
 
LVL 58

Assisted Solution

by:Gary
Gary earned 250 total points
ID: 39913702
Yep. As Ray says you obviously need to be checking it is the right format for the field.
0
 
LVL 6

Author Closing Comment

by:M. Jayme Nagy
ID: 39913711
thanks guys

taking this in baby steps

i dont want to get caught up and then realize later i forgot a huge issue
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now