{"id":421,"date":"2022-08-30T15:01:04","date_gmt":"2022-08-30T15:01:04","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2013\/11\/09\/why-can-i-query-with-an-int-but-not-a-string-here-php-mysql-datatypes-collection-of-common-programming-errors\/"},"modified":"2022-08-30T15:01:04","modified_gmt":"2022-08-30T15:01:04","slug":"why-can-i-query-with-an-int-but-not-a-string-here-php-mysql-datatypes-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2022\/08\/30\/why-can-i-query-with-an-int-but-not-a-string-here-php-mysql-datatypes-collection-of-common-programming-errors\/","title":{"rendered":"Why can I query with an int but not a string here? PHP MySQL Datatypes-Collection of common programming errors"},"content":{"rendered":"

I am working on an Asset Database problem. I receive $id<\/code> from $_GET[\"id\"];<\/code> I then query the database and display the results.<\/p>\n

This works if my id is an integer like “93650” but if it has other characters like “wci1001”, it displays this MySQL error:<\/p>\n

Unknown column ‘text’ in ‘where clause’<\/p>\n

All fields in tables are of type: VARCHAR(50)<\/code><\/p>\n

What would I need to do to be able to use this query to search by id that includes other characters?<\/p>\n

Thank you.<\/p>\n

\n<\/code><\/pre>\n
__<\/p>\n
\/* \n*  View Asset\n*\n*\/\n\n# include functions script\ninclude \"functions.php\";\n\n$id = $_GET[\"id\"];\nif (empty($id)):$id=\"000\";\nendif;\nConnectDB();\n$type = GetAssetType($id);\n\n?>\n\n\n\n\n\n\nWagman IT Asset\n\n\n\n    \n                \n                  \n                \n\n                \n\n                \n                    \n\n                        \n                        \n                            <\/code><\/pre>\n
    \n\n\n
    <\/td>\n\n
      \n
    • Search Assets<\/li>\n<\/ul>\n<\/td>\n
    		\n
      \n
    • Add Asset<\/li>\n<\/ul>\n<\/td>\n
    		<\/td>\n<\/tr>\n<\/table>\n<\/ul>\n
    \n                        \n                        \n                        <\/pre>\n
      \n
    • \n
      View Asset<\/code><\/h1>\n<\/li>\n<\/ul>\n
      \n                        \n\n\n                        \n\n                        \n                \n                \n                \n                    
       \n                \n                \n                \n                Wagman Construction - Bridging Generations since 1902\n                \n\n\n\n\n<\/pre>\n
        \n
      1. \n
        Quote the variable, like this:<\/code><\/p>\n
        WHERE asset.id = '$id'\n<\/code><\/code><\/pre>\n<\/li>\n
      2. \n
        You have a SQL Injection vulnerability.<\/p>\n
        You need to use parameterized queries, using PDO.<\/p>\n
        You also need to HTML-encode your data, using htmlspecialchars<\/code>.<\/p>\n<\/li>\n
      3. \n
        As others have mentioned, simply quoting with single-quotes is a big security risk. Use mysql_real_escape_string on the data beforehand or else use an extension like PDO with parameterized statements that will automatically be quoted.<\/p>\n
        And while sanitizing (with htmlspecialchars) is not necessary before storage (and I don’t recommend it in case you need it reverted to its inputted state) you should sanitize it before output so that HTML\/script tags won’t be parsed.<\/p>\n<\/li>\n<\/ol>\n
        Originally posted 2013-11-09 19:14:52. <\/small><\/p>","protected":false},"excerpt":{"rendered":"
        I am working on an Asset Database problem. I receive $id from $_GET[“id”]; I then query the database and display the results. This works if my id is an integer like “93650” but if it has other characters like “wci1001”, it displays this MySQL error: Unknown column ‘text’ in ‘where clause’ All fields in tables […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-421","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/421","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=421"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/421\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=421"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=421"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=421"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}